Automated .txt file into .csv file into predefined format

[ankrups]

I have product file in .txt format containing product information such as SKU no, description, dimensions, barcode etc. Each week I need to amend the file into my inventory system which reads predefined .csv file upload. I need to create a macro OR an automated method where when I run macro which should copy the data from .txt file and input the data into .csv file to particular columns.

Can anyone help me on this, please?

 

[rondeondo]

Hi
I would set up a translation file with two sheets. Sheet one, source, copy your text file into here. use text to columns to split it, assuming it's tab delimited etc.
sheet two, destination. set up formulas to pull data from sheet one into the columns in the order you need. copy the formulas down as many rows as you need.
Each update, drop your data in and save out sheet two as a csv file.....

 

[ankrups]

Thanks Ron. So do you want me to send you the sample file before you further.?

 

[rondeondo]

sure, send a sample of your text file you receive and the csv output you need

 

[ankrups]

your email please?

 

[rondeondo]

just paste a few rows of each, that will be fine. I can copy and paste from the message

 

[ankrups]

input file (TXT File sample Data)
Field Value Dynamic Supplies Code CI521GY Description Canon CLI521 Grey Ink Cart Reseller Price Ex GST 13.77 Manufacturer Name Canon Product Category description CON-INK Product Weight (kg) 0.035 Manufacturer OEM Code CLI521GY Printer Compatibility CANON PIXMA MP980,CANON PIXMA MP990 Cartridge Yield 1,370 pages Cartridge Type Grey m3 (L x W x H) - Not cubic weight 0.00032 Regionalisation Code CAN-INK-RE Length (m) 0.12 Width (m) 0.09 Height (m) 0.03 SOH Brisbane >25 SOH Melbourne >25 SOH Perth 18 SOH Adelaide 5 SOH Sydney >25 GTIN Barcode 4960999682044

my inventory system version (CSV File)

Item Type

Product ID

Product Name

Product Type

Product Code/SKU

Bin Picking Number

Brand Name

Option Set

Option Set Align

Product Description

Price

Cost Price

Retail Price

Sale Price

Fixed Shipping Cost

Free Shipping

Product Warranty

Product Weight

Product Width

Product Height

Product Depth

Allow Purchases?

Product Visible?

Product Availability

Track Inventory

Current Stock Level

Low Stock Level

Category

Product Image ID - 1

Product Image File - 1

Product UPC/EAN

Hope this helps.

 

[rondeondo]

is that how you receive the data? does it have end of line chars or any other separators?