Converting basic to delimited text files 1

[meldrape]

Here's a challenge. I am working on a huge project converting basic files to SQL 2000 database tables. Some of the files I am converting have "place holders" for the fields. For example, I have a customer table that has mostly delimited fields when I did a text dump, except there are 8 fields that have "placeholders", so they were not delimited and got lumped into one field. Does anybody have an idea of how to parse out that data? I have the file layout, so that helps because I at least know what length each field is supposed to be, but I don't know where to start. Do I write some sort of algorythm to do this first, then convert again later? Any thoughts would be greatly appreciated. (I could use Access or Excel.) Thanks.

 

[Nikita6003]

Hiya meldrape,

what d'you want to use to load the files into SQL? If it's bcp or somesuch routine your best bet wuold be to write some code to open the text file in Excel (this'll automatically convert to columns), then write a quick routine to convert your 8_fields_lumped_into_one back to 8 columns (use something simple like the

Mid

function - if it's all fixed width you can determine the field break point quite easily), then save again as csv.
Quick, easy and little coding

HTH

Cheers
Nikki

 

[meldrape]

That helps a lot. I needed a little direction like that. I'll try the mid function idea. I've never used BCP but I will try that as well. Many thanks.

 

[Nikita6003]

BCP = cool

BULK insert of loads of records taking hardly any time ;-)
we use it a lot for straight dumpt in our (SQL and Sybase) databases

If you need more help, let us know

Cheers
Nikki