Loading Massive Fixed Width File

[shawn]

Here's the deal. I have a fixed width file with roughly 1500 columns (it's from a vendor). I need to load this file into a database (I'll worry about normalizing later -- I just need to get this puppy into a DB so I can do some analysis). I have the file layout.

How in the heck can I use the file layout to help me create the table and import the data? I know there must be a way, I just don't have any clue where to start. I have access to MySQL and can script in VBScript if that helps -- but I'm sure there must be an easier way.

An example of a line from the file layout:

AA	 0025	0130	Organization Name

0025 represents the start position and 0130 represents the field length.

Any help would be ENORMOUSLY appreciated!!!

[greg.harvey]

Could you not import it in to MS Excel? Then you could spit it out again as a CSV and MySQL will swallow a CSV easily.

[MarcusJT]

I have a plan so cunning, you could stick a tail on it and call it a weasel!

Here's how it goes...

- Create a new table using ADOX
- Read the layout file and split each line into the four constituent parts using RegEx
- Assuming no extra carriage returns, the number of lines in the layout file will equal the number of fields (which I will call "N"), so dimension 4 arrays to be of size N-1 (minus one because of the 0-based array index). I'll call these arrays "name array", "length array", and "data array".
- For each line, use ADOX with the captured values to dynamically add each field to the table with the correct name and length (data types might need some thought, but if they're all text then there's no problem!). At the same time, add the field name and field length to the correct position in the name and length arrays as created above.
- We should now have an empty table ready to accept data
- Load the (empty) table into an (updateable) recordset.
- Open the input data file (use an ADO.Stream object for maximum efficiency) and read a line at a time into a variable.
- For each line of data, set a position counter (C) to 0, loop from P = 0 to N-1, grab the length of the field (L) from the length array (at position P), read that number of characters from the current position (using MID), and store the substring in the data array (at position P), add L to C, and then use the Recordset.AddNew method to add the data to the table in one fell swoop by using the field name and field data arrays as parameters.
- At this point we should have reached the end of the data file, so just clean up/close your used objects and you're done!

Any questions?

Baldrick, you wouldn't know a cunning plan if it danced naked on a table shouting "Cunning plans are here again!"

[shawn]

Marcus,

Thanks -- I was afraid that's what you were going to tell me to do.

Wish me luck (I'll be wishing for an easier way)!

Shawn

[MarcusJT]

It's really not that hard. I could (probably) write it in about an hour, in about 100 lines (or less). It just sounds complicated because it's in English, not code. I've already done what is (arguably) the hardest part for you, so give it a go - we're all here to help.

PS - Good luck!