Importing / Exporting Data from text file in VBA - No Wizards!

[texuspete00]

For some crazy reason someone wants me to import a file that is BOTH fixed length and delimited with a vertical bar "|", and export it to a tab delimited file type. It's like a riddle, as I'm not allowed to use docmd.transfertext at all. I been reading some examples on parsing text files with VB, figuring I could then go and make my own text file for export, but everything I seem to find is written for real VB programs but not the stripped VBA.

Not trying to get anyone to do my work for me.... just if anyone has a basic idea where I should go. Like... am I on the right track with trying to parse it? Am I just not using the proper add-ins that would allow me to use the text file parser and so on?

I don't know why it's both fixed length and delimited. Seems redundant. If I end up dealing with it as fixed length I guess there would be fields with a length of 1 to capture the bar in between the length of the rest of the fields when I specify them. The bar as a delimiter seems easier when using the wizards but out side of there... not so sure. Coding examples on stuff everyone else uses a wizard on is hard to come by.

[Entity_Razer]

so you need to do WHAT? Because i'm not following you here....

Could be the fact that a bit of what you where saying got lost in the translation but I found your post quite gurbeld and hard to read. But hey that could just be me ;)

I think you want to import (actually convert)a text file that is limited in size (characters?) and something with "|" and convert it to a filetype that does not have those limitations. Right?

Well for the limited size I can't help you and I can't give you a quick fix but I suggest for the "|" thingy there is a usefull stringfunction in VB.net which lets you replace it or do with it what you want.

like this:
sString.replace("|"," ")

Thus replacing "|" with a space. Now I don't know about making that into an enter..... I should ask my teacher concerning that...... lemme ask him tomorrow..

For the importing, I'm looking into that myself but can't you just use read in VBA?
to read a file?

you'll have to ask the programming gurus for more help because thats about all I can give you at the moment.

[texuspete00]

Yeah when I heard going from vertical bar delimited to tab delimited my first inclination was to just import into a table then export. Then I got confused they don't want me to use docmd.transfertext!!! But I come to realize that yes you are right, telling me not to use the command was more of a "hey, don't import and then export, but convert."

So I set up the file system object, and used the text stream method. After the file system object is setup, I read in a line at a time via a loop. In that loop a function is called that deals with each line one by one.

Naturally that function is itself a loop that goes through the line. I use the "Instr" function to find out where my first vertical bar is(I call it my RightBarrier), and write to output the left of that line, with a tab of course. Then I enter my loop, find the next vertical bar by using my previous barrier as the starting point for the Instr function to look past. There is a conditional here, If there is another bar found, my rightbarrier now becomes my left, and the new found bar is the new rightbarrier. With this knowledge I can execute the mid function with these barrier points. When the instr function returns zero, I hit my else in the loop, which means I know it's time to grab the right side of the string. Subtract the length of the whole string from the position of the last found bar and I now have my last figure I need to execute the right command. Write the end line character and return to the main loop that reads a line, until finito. :D