Reading the text file (Part 2)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Reading the text file (Part 2)

Author

Topic

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-04 : 06:13:04

here is the text file

How can i loop it and get the data with Red color box?

NOTE: i wan to get each data above and column data (Tbl1col1....Tbl2col2), after that i want to store each of the data into my database

Hope you all can guide.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-04 : 10:11:12

1) BULK INSERT
2) SKIPROWS = 3

Then use substring or any other technique to distinguish the data.
Is there a TAB between the "columns"? If so it is very easy.

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-04 : 10:25:03

yes, peso. There have a TAB between the columns!

can u show me the VB.net coding, hw to get those items i want?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-04 : 10:49:54

1) OPEN FILE
2) READ
3) CLOSE FILE
4) Use JOIN function to convert a file content to an array A
5) For i = 3 to 99999
v = SPLIT(A(i), vbtab)

Now you can iterate all content for the requested rows.

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-04 : 20:19:04

thanks peso ideas.

before i want to grab all the column items. I also need grab the No. & File, as picture show below:

between, let say my File name is like this: File: abc_F.file
how i convert to this: abc/F

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-05 : 03:28:38

The first JOIN function get all rows into an array A!

To get the "1234"... SPLIT(A(1), " ")(1)
To get the "ABC"... SPIT(A(2), " ")(1)

For i = 3 to ubound(a)
v = SPLIT(A(i), vbtab)
for k = 0 to ubound(v)
...
next
next

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-06 : 04:04:34

thanks peso again. It work.

But i am using the ArrayList to store each of the SQL. Eg:


Dim aSQLMaster As New ArrayList
StrSQL = "INSERT INTO NXP_AM1_ACU_DETAIL " _
& "(FromId,P001,P002,P003,P004,P005, " _
& "P006,P007) VALUES ('" & UniqueId & "', " _
& "'" & aColumns(5) & "', '" & aColumns(8) & "', " _
& "'" & aColumns(11) & "', '" & aColumns(14) & "', " _
& "'" & aColumns(18) & "', '" & aColumns(21) & "', " _
& "'" & aColumns(24) & "')"
aSQLMaster.Add(StrSQL)

But i get the error is: Index was outside the bounds of the array

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 04:10:00

Are you sure you are using LBOUND and UBOUND?

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-06 : 04:13:46

peso, actually i just use your concept and match with my concept.

I didn't use the LBound and UBound.

Is that will affect the ArrayList problems?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 04:38:22

Of course!
If you use FOR NEXT and the variable for incrementing the loop is greater (or less) than the array boundaries, you will get an error.

Programming 101.

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-06 : 06:29:19

peso, actually my code something like this:


Dim clsReader As TextReader = File.OpenText(txtFile.Text)
                Dim sLine As String = clsReader.ReadLine()
                While sLine <> Nothing
                    If sLine.StartsWith("Data") Then
                        sData = sLine.Substring(0)
                    ElseIf sLine.StartsWith("Order") Then
                        sOrder = sLine.Substring(11)
                    ElseIf sLine.StartsWith("Job") Then
                        sJob = sLine.Substring(11)
                        sJob = Path.GetFileNameWithoutExtension(sJob)
                        sJob = sJob.Replace("_", "/")
                        Dim DT As DataTable = Nothing
                        pmCon.QueryMe("SELECT UniqueId FROM NXP_AM1_ACU_MASTER WHERE " _
                                & "DeviceType='" & sJob & "'", DT)
                        If DT.Rows.Count <> 0 Then
                            UniqueId = Trim(DT.Rows(0).Item(0))
                        End If
                    Else
                        aColumns = sLine.Split(Char.Parse(" "))
                        If aColumns.Length <> 0 Then
                            StrSQL = "UPDATE NXP_AM1_ACU_MASTER SET " _
                                     & "BatchNo='" & Trim(sOrder) & "', " _
                                     & "TrackNo='" & aColumns(0) & "', " _
                                     & "SortNo='" & aColumns(1) & "', " _
                                     & "BinNo='" & aColumns(2) & "', " _
                                     & "FinalResult='" & aColumns(3) & "', " _
                                     & "ACUFileName='" & Trim(sOrder) & ".Dat" & "' " _
                                     & "WHERE UniqueId='" & UniqueId & "'"
                            aSQLMaster.Add(StrSQL)

                            StrSQL = "INSERT INTO NXP_AM1_ACU_DETAIL " _
                                        & "(FromId,P001,P002,P003,P004,P005, " _
                                        & "P006,P007) VALUES ('" & UniqueId & "', " _
                                        & "'" & aColumns(5) & "', '" & aColumns(8) & "', " _
                                        & "'" & aColumns(11) & "', '" & aColumns(14) & "', " _
                                        & "'" & aColumns(18) & "', '" & aColumns(21) & "', " _
                                        & "'" & aColumns(24) & "')"
                            aSQLMaster.Add(StrSQL)
                        End If
                    End If
                    sLine = clsReader.ReadLine

Can you help me and see where is the error?
Index was outside the bounds of the array

actually, if i don't want use the ArrayList, using the datatable, how it can pass to the database with BATCH?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-06 : 06:40:34

I guess just checking aColumns.Length <> 0 is not enough. You need to make sure that the array has 25 (or whatever max no. of ) columns.

If aColumns.Length>=0 and aColumns.Length<=24 then
...
End IF

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-06 : 09:16:10

you means the error occurs because i mis-type above statement?

i already try it just now, also same.!

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-06 : 23:25:10

okay, i know the problems is at where, here is my loop until 6:

for my text file result i want to grab is like this:

the file above show me problems code is here

aColumns = sLine.Split(Char.Parse(" "))

how i ignore no matter how many SPACE in between the data also inlcude ONLY ONE SPACE?

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-07 : 01:16:05

i solve it with Split it with Multiple Space

anyway, thanks you all point my error, so i can direct go to alter my errors

really thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 02:13:49

Or, you can use single space as delimiter and later, in the loop, skip the rows with empty space in it?
AS you can see, not all columns has double spaces, like the ones that begins with a minus sign.

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-07 : 09:56:48

quote:
Originally posted by Peso

Or, you can use single space as delimiter and later, in the loop, skip the rows with empty space in it?
AS you can see, not all columns has double spaces, like the ones that begins with a minus sign.

Peter Larsson
Helsingborg, Sweden

thanks peso again...............the part 1 text file i already done it with you all suggestion....

now i face this text file format again....

hope you guide me some line of coding (vb.net)
---------------------------------------------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 10:38:17

For row loop, split with ":" for row 2 and 3.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 10:38:40

Or don't split at all, since it seems the position is fixed.

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-02-07 : 19:55:52

quote:
Originally posted by Peso

For row loop, split with ":" for row 2 and 3.

Peter Larsson
Helsingborg, Sweden

how I loop each of the different text file which have how many rows?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 01:08:36

You have a loop counter to iterate through all rows, right?
Top row is 0 and then you just count down to the second and third row, which has loop counter value of 1 and 2.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources