How can I convert this file?

kcarpy

Registered User.
Local time
Today, 14:00
Joined
Feb 7, 2008
Messages
13
Greetings. I have an issue with getting data converted to some sort of format that would be more friendly to databases than its current form, and I'm wondering if anyone here would have some advice.

It's a big dataset, around 200,000 records, and they are in tab-delimited format. Unfortunately, they are all on one line. That is, the end of line character is also a tab, so it is indistinguishable from the delimiter. There are only five variables. Is there any program or other way, perhaps even using Access, to tell a program that every fifth tab starts a new record? It seems like there should be something, but I don't know what it would be.

Any help with this would be greatly appreciated. Thanks.
 
I'd use MS Excel's wizard to seperate it to rows and columns, then import it to access
 
What can Excel's wizard do that Access's can't? I don't see anything that will let me define end of line information, just set delimiters. Am I missing something?
 
In MS Excel, go to the Data tab and you can convert text to columns
 
Yes, I can get to that. It's not that I need to convert the data into columns. That's simple, and Access can do it too. It's that I need a way to convert a very long single line of data into multiple rows at every fifth delimiter. I don't see any way of doing that.

Right now it's like this:
Code:
var1 var2 var3 record1var1 record1var2 record1var3 record2var1 record2var2 record2var3
I need to convert it to:
Code:
var1 var2 var3 
record1var1 record1var2 record1var3 
record2var1 record2var2 record2var3

It's tab-delimited, and the field lengths vary from record to record.
 
This is actually a trivial problem for any scripting language. I know perl will work, I suspect VBScript might and god forbid, I bet even JavaScript would do in a pinch. If you want to cut your teeth on something more powerful like C, C++ or Java those would certainly work as well.

With whatever language you use this is how you would tackle that problem:

Open your file with your scripting language and put it in a string variable
Create a while loop that reads through your input string and replaces every 5th tab with a line break
Write that string variable with line breaks in it to a new text file.

Of course you are going to have to learn the specifics of whatever language you choose to implement that general solution, but there should be many tutorials online for whichever language you choose.
 
Ah, okay, so there's nothing native to Access that works. I thought maybe I was missing something. It's past time I started learning some scripting languages, and this sounds like a good way to start.

Thanks!
 
Oh I'm sorry I misunderstood you then

It can be done with access, you'll need functinos like instr, mid and some looping. I"ll be more than happy to help you out with it if you can post a sample
 

Users who are viewing this thread

Back
Top Bottom