Converting catalog files

[ncscott]

While it's not EDI-specific, this seems the most appropriate forum to find advice pertaining to an e-procurement purchasing system. The system I work with is web-based and can use either XML or EDI for document transport.

Anyway, my dilemma is pretty simple. The best way I can import a one-time manual catalog file is in CIF 3.0 format. However one of my vendors has informed me that all they can provide is a tab-delimited flat file with double-quote text delimiters. Of course getting it CLOSE to CIF (comma-delimited, no text delimiters, adding header and footer) was no problem, unfortunately the removing of text delimiters has left me with a file in which many lines do not have the proper number of line items. This is a file with thousands of lines and fixing it manually would take forever.

Has anyone heard of any converter applications out there that can create a text file in CIF 3.0 format?

 

[damber]

a CIF 3.0 file (Catalog Interchange Format) is essentially a csv formatted file (comma separated values) with some additional headers and footer.

If you open up Excel and import the tab delimited data file so that each tab delimited field is inside an individual column you will have half the job done.

Once in Excel, add column headers with prefix of FIELDNAMES: in the first column (with the first column name) with the EXACT NAMES expected by your app. Then move the columns to the correct order that is expected (some apps don't care what order - as they use the header tags, but do it just to be safe).

Then add all the header stuff (CODEFORMAT, LOADMODE etc) (but make sure the entire value for each header row is only in one cell.

And finally use Excel to save as a CSV.

It will separate out the columns as necessary to satisfy the CSV format.

The reason you have varying column counts is because some of your data fields have a comma in them and are not being encapsulated / released properly.

example:
- first 6 lines inside column A
- FIELDNAMES: Supplier ID in column A the rest in following columns
- DATA in column A
- your imported and sorted/organised columns in individual cells
- ENDOFDATA in column A

CIF_I_V3.0
CODEFORMAT: UNSPSC
LOADMODE: F
COMMENTS: ABC Company
ITEMCOUNT: 1
TIMESTAMP: 2005-09-27
FIELDNAMES: Supplier ID, Supplier Part ID, Manufacturer Part ID, Item Description, SPSC Code, Unit Price, Unit of Measure, Lead Time, Manufacturer Name, Supplier URL, Manufacturer URL, Market Price, Short Name, Currency, Parametric Data, Parametric Name, Delete
DATA
012345678,12345,12345,This is a test.,80141605,15,EA,5,ABC Company,,,,Test Item,USD,"{COLOR=""BLUE"";SIZE=""XL"";}",Corporate Apparel,f
ENDOFDATA

Open in Excel: File > Open > (Select Text Files in the File Type) > Import Delimited > Tab Delimited

Save in Excel: File > Save As > (Select CSV File Type)

You might have to tidy up the headers a bit, but this should be simple.

A smile is worth a thousand kind words. So smile, it's easy!