Creating Array for text string.......??? LAG!

[opiate]

I'm creating a program for work and need to import a flatfile(normal text file) into Oracle. I'm using VB. The flatfile is delimited and the first row is the field heading, the delimiter is comma and the text qualifier is ".

When the mainframe kicks out the nightly export or "flatfile", I need my app to pick it up and import it into an Oracle table. I can get my app to read the flatfile but how do I create a table based on the fields in the flatfile? Can I create an array for this? How would I do it?

Thanks,

opiate

[lysergic]

This is from ASP so it should work for what you want in VB, or at least point you in the right direction hopefully.

You want to use a FileSystemObject to read the file in with something like this:

Set TS = FSO.OpenTextFile(strFileName, ForReading, Create)

This will read a line at a time and place the lins into an array called strLine by

Do While not TS.AtendOfStream
strLine = TS.ReadLine 'Reads one line from file at a time

Then you will want to use a split command to create another array that will sepperate the line by your delimiter. Then just wend the loop to read in the next line.

Once you have your array populted then it should be an easy task to send your values upto the database either in the same loop or a seperate loop.

Hope that helps.

[opiate]

Yes, I understand your code. My question is, How do I get the first line of the text file into an array so I can create a table based on the first line fields.

1st line of text file will run something like so, my syntax is off a little

"myarray" is the name of the fields

sSQL = & _
"CREATE TABLE tmp_replist (" & _
" & myarray(1) & " varchar2(30), & _
" & myarray(2) & " varchar2(30), & _
" & myarray(3) & " varchar2(30))"

Then on the rest of the lines that are read in the loop are inserted into "myarray" fields. It looks like I'll need to create antother array here?

The text file is already delimited so I don't think I need the Split command to create a seccond array, or do I? How do I use it?

Thanks for the help

[lysergic]

Yes, you will need another array. Your first array is just the delimited line of text. You then need to create another array to break up that line.

Ok. Here is a sample of a file I have:

1,2,3,4,5
a,b,c,d,e

Using this

x=-1
' Instantiate the FileSystemObject
Set FSO = Server.CreateObject("Scripting.FileSystemObject")

' use Opentextfile Method to Open the text File
Set TS = FSO.OpenTextFile(strFileName, ForReading, Create)
If not TS.AtEndOfStream Then
Do While not TS.AtendOfStream
x=x+1
strLine = TS.ReadLine ' Read one line at a time

we would have a 2 dimension array for strLine with
strLine(0) = 1,2,3,4,5

Then use this

Dim BatchArray, value1(),value2(),value3(),value4(),value5()
BatchArray = Split(strLine, ",", -1, 1)
value1(x)=BatchArray(0)
value2(x)=BatchArray(1)
value3(x)=BatchArray(2)
value4(x)=BatchArray(3)
value5(x)=BatchArray(4)
wend
end if
TS.Close
Set TS = Nothing
Set FSO = Nothing


On the second time through strLine = a,b,c,d,e and the values of value1(x) would then be changed as well.

So in the end value1(0) = 1, value2(0) = 2, etc
and value1(1)=a, value2(1)=b, etc.


Then use something like a store procedure or however you are getting your data into your database with something like loop with your upper value equal to x.

Also I am sure there is a better way to do this because as you can see with this method you are creating alot of arrays (more then 2 actually) and I only use it when I need to (for processing of credit card batch files. So I know it will not be used more then once daily by an admin) If this is something that is used alot then I would look for a better way of doing it.

[antonye]

You should really use the Input # function as this allows you to define a set of variables to populate with your delimited file.

This saves you doing any Split functions (which won't take into account any delimiting " characters) and it also automatically strips the quotes for you on text.

For example, if your line is:

"This is, some text",12.35,"Hello",72

Then you can do this:

Input #iFile, sHeader, lPrice, sTitle, lRef

You will then find that:

sHeader = "This is, some text"
lPrice = 12.35
sTitle = "Hello"
lRef = 72

In the example given by Lysergic above, you will split the first string on the comma which is within the quote-delimiters, giving you more variables than there actually should be and you'll end up with incorrect data.

Don't bother trying to rewrite a file parsing routine when there's already one built in!!

See: Input # Statement (http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbenlr98/vastminput.htm) on MSDN.

[lysergic]

Knew there had to be a simpler way to do that part of the code and you are right that will work better. Much lower overhead. Although you are mistaking about the method I posted not working as I have used it in the past with no problem. But did just see what they are using for a delimiter. Could try something like split (str, chr(34)&".",-1,1) for the slit function or something such, but this is a moot point as I agree that your method would work much better.

[opiate]

Lysergic,

I cannot get the 2nd dimension to work.


strLine(0) = 1,2,3,4,5


Is there another way to do the samething?

[lysergic]

I screwed up in that post. That is not an array. That is a sring that is equal to the line of delimited text.

so first time through strLine=1,2,3,4,5
2nd time through strLine=a,b,c,d,e

Then the way I have it strLine is split into an array using a split function. Although as posted above if using VB then use the input # function as pointed out by Anyonye. Sorry for the confusion.

[opiate]

I'm going to use ODBC Text Driver with ADO. Screw this, arrrrgggggg.... I sat at my desk for 4 hours straigh figuring this out. I even missed lunch.......slurp!

Thanks for everyones help. I did get alot out of it. :D :D :D :D :D :D :D