Cannot import all records of a TAB delimited text file into SQLite table

[Mahares]

When importing a TAB delimited text file which is attached, into a SQLite table using Regex.Split(Chr(9), line) and a textreader, the rows that have the last column blank do not get imported. In other words, Regex does not seem to be able to parse the text file line. It is skipped. I avoid using another delimiter like comma or semi colon for fear they exist in the records. What would you recommend.
The table has 4 fields as follows:
CODE , TEXT PRIMARY KEY
CHEMICAL ,TEXT
PRICE, REAL
UNIT, TEXT

Thank you

 

[Mahares]

I removed the error trapping code. Here is what the error shows:
arrayindex out ofbounds exception with the delimiter as TAB.When I recreate a similar file with the same data , but with a '|' pipe verical bar as a delimiter Regex.Split("|", line) or Regex.Split(Chr(124), line) , the error message was: Constraint exception, error code 19, constraint failed.I hope the additional information will help someone shed some light on what I should do. It looks like the Regex function is a trouble maker.
Thanks brave guys

 

[NJDude]

The pipe is a "reserved" regex symbol (read THIS) you need to escape it (Regex.Split("\|", line)) or use a different character.

 

[Mahares]

I had the same trouble using the TAB (chr(9)) if you look at the beginning of this thread. I was trying something other than the tab, simply to reach out for something. I actually never use the pipe. I always like to use the TAB because the text file can have commas or semicolons. I think the main problem is with the the RegEx function. Whenever the last column field is blank in any row, it returns the error. What should I use instead of the TAB (forget the pipe) when I have blank cells at the end of some rows of a text file?
Thanks

 

[Erel]

You can use:

Regex.Split(TAB, line.Trim)

This will remove the extra whitespace.

 

[Mahares]

Regex.Split(TAB, line.Trim

The above recommendation does not work. Please take a look at the tab delimited text file I attached at the beginning of this thread by opening it via Notepad. Two of the rows have nothing in the last column. That is what is causing the problem. The error I am getting is: ArrayIndexoutofbounds. It seems that RegEx must see something in the last column to make the closure and allow the import. If there is nothing in the last column, even for one record, it does not work. I hope this makes it clearer for you to make a determination if it is RegEx deficiency and if so what can I do instead.
Thanks

 

[Erel]

Without you posting your code we can only guess what is the problem. You are getting an ArrayIndexOutOfBounds error because the array is smaller than you think it is (which is expected if there is a row with less columns).

 

[Mahares]

That is correct Erel. If there is no data on the last column in one of the rows, the IMPORT into the SQLite table does not take place. There is no need to burden you with my code, because I spent a full day with a multitude of tests with different delimiters. It happens with all of the delimiters:ArrayIndexoutofbounds
If I fake it and make sure I put something in the the last column of every row to bound the text file, it will work, but that is not the proper way.
My question is: Do you have a more reliable method of IMPORTING a tab delimited text file where it is possible that a row may have no data in the last column? RegEx is definitely not the answer.
Thanks

 

[Erel]

I cannot help you without seeing the code. RegEx is a general tool to parse text. It is up to the developer to use it correctly.