Create Text File with Variable Field Positions

[Bonediggler1]

Hello-

I need to import a CSV file that has Invoice and Line Detail data in the same fields, and then create a delimited text file in which the Invoice and Line detail fields have different spacing.

CSV file Example (Invoice and Line data is in same fields though does not appear as such in this post)

INVOICE ID, INVOICE #, CLAIM #, DATE OF SERVICE, INVOICE TOTAL
LINE ID , INVOICE #, CLAIM #, CODE 1, , CODE 2

Text Output Example:

INVOICE_ID INVOICE_# CLAIM_# DATE_OF_SERVICE INVOICE_TOTAL
LINE_ID INVOICE_# CLAIM_# CODE_1 CODE_2


Thank you!!

 

[SkipVought]

hi,
quote]then create a delimited text file[/quote]
delimited by WHAT?

What is the spacing requirement that you infer?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

That's because the 2 commas between CODE 1 and CODE 2 signify that something else should go there. The way around that is when you import the file (assuming it's being imported somewhere), you'd specify "treat consecutive delimiters as one"... or that's the way it is in Excel, at least.

I'm assuming that you mean commas as your delimiter based on the example and based on the .CSV file format. However, it is possible to export with other delimiters.

 

[Bonediggler1]

Thanks guys.

I think the way to do it is split the Invoice and Line records into different tables, build the Invoice/Line records with correct padding in VBA and then insert into a single field in a new table.

I'll keep you posted...