How to open a .CSV file ?
in [VB.NET]
|
Prev: Coversion problem
Next: Outlook PST Files
From: GhostInAK on 11 Oct 2006 03:21 Hello Cor Ligthert [MVP], Yes, because using a semicolon as a record separator in a COMMA-SEPARATED VALUE file is a sane thing to do. Doesn't matter.. Value One; "Value; Two"; Value Three would produce identical results. -Boo >> -Boo >> >>> Why not just use a StreamReader class and parse the values at the >>> commas? >>> > Be aware that this is in the non English speaking cultures mostly not > true. In those cultures the ";" is used as field delimiter. > > Cor >
From: Cor Ligthert [MVP] on 11 Oct 2006 12:35 Boo, I did not invent that, it is just as it is implementend in countries where the comma is a decimal seperator (the most). A true CSV file uses a comma as seperator for numeric fields whithout and than the comma cannot be used in a CSV file as decimal seperator. Probably they should in not English speaking countries call it otherwise by instand a PuntComma gesepareerd bestand. But some letter combinations have an international meaning without that the real characters real are meaningful. Cor "GhostInAK" <paco(a)paco.com> schreef in bericht news:c71747b44af368c8bada033cfc80(a)news.microsoft.com... > Hello Cor Ligthert [MVP], > > Yes, because using a semicolon as a record separator in a COMMA-SEPARATED > VALUE file is a sane thing to do. > > Doesn't matter.. Value One; "Value; Two"; Value Three would produce > identical results. > > -Boo > >>> -Boo >>> >>>> Why not just use a StreamReader class and parse the values at the >>>> commas? >>>> >> Be aware that this is in the non English speaking cultures mostly not >> true. In those cultures the ";" is used as field delimiter. >> >> Cor >> > >
From: Scott M. on 11 Oct 2006 17:34 You can use this technique to parse the file at any character, it doesn't have to be the comma. "GhostInAK" <paco(a)paco.net> wrote in message news:be1391bf1c1ee8c8ba8d6a850af6(a)news.microsoft.com... > Hello Scott M., > > Because not all CSV files are supposed to be parsed at the comma: Value > One, "Value, Two", Value Three > > OP, your connection string is wrong. Try: > Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text; > > -Boo > >> Why not just use a StreamReader class and parse the values at the >> commas? >> >> "fniles" <fniles(a)pfmail.com> wrote in message >> news:%23pruw%23K7GHA.4708(a)TK2MSFTNGP05.phx.gbl... >> >>> I have a .CSV file (comma delimited) that I want to open using OLEDB, >>> but I >>> get the error "External table is not in the expected format." >>> If I save the .CSV file to an .XLS file, I can open the connection >>> with no >>> problem. >>> What is the correct way to open a .CSV file ? >>> If I can not open the CSV file, how can I programmatically save the >>> CSV >>> file to an XLS file ? >>> Thanks a lot. >>> dim myCon OleDb.OleDbConnection >>> myCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data >>> Source=c:\file.csv; Extended Properties=""Excel 8.0; HDR=NO; >>> IMEX=1""") >>> --> error "External table is not in the expected format." > >
From: GhostInAK on 11 Oct 2006 21:05 Hello Scott M., Well, yes, you could write your own CSV parser as MDO did.. but that would serve no practical purpose other than to teach you how to write a string parser. I assume when you said "parse at the comma" you meant string.split. While you could use this function, it would be stupid to use it on a CSV file. Quoted values are going to kill you. It's not worth it. -Boo > You can use this technique to parse the file at any character, it > doesn't have to be the comma. > > "GhostInAK" <paco(a)paco.net> wrote in message > news:be1391bf1c1ee8c8ba8d6a850af6(a)news.microsoft.com... > >> Hello Scott M., >> >> Because not all CSV files are supposed to be parsed at the comma: >> Value One, "Value, Two", Value Three >> >> OP, your connection string is wrong. Try: >> Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended >> Properties=Text; >> >> -Boo >> >>> Why not just use a StreamReader class and parse the values at the >>> commas? >>> >>> "fniles" <fniles(a)pfmail.com> wrote in message >>> news:%23pruw%23K7GHA.4708(a)TK2MSFTNGP05.phx.gbl... >>>> I have a .CSV file (comma delimited) that I want to open using >>>> OLEDB, >>>> but I >>>> get the error "External table is not in the expected format." >>>> If I save the .CSV file to an .XLS file, I can open the connection >>>> with no >>>> problem. >>>> What is the correct way to open a .CSV file ? >>>> If I can not open the CSV file, how can I programmatically save the >>>> CSV >>>> file to an XLS file ? >>>> Thanks a lot. >>>> dim myCon OleDb.OleDbConnection >>>> myCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data >>>> Source=c:\file.csv; Extended Properties=""Excel 8.0; HDR=NO; >>>> IMEX=1""") >>>> --> error "External table is not in the expected format."
From: Scott M. on 11 Oct 2006 21:54
Well, not really. Quoted values (and single quotes and any other special character) could be escaped before any parsing took place. As for the .Split method of a string, it most certainly would serve a practical purpose, it wold split the string at whatever character it is told to. The fact is that we are talking about a delimited file here and that means that the delimeter is a known character - this is exactly what ..split() is for. Now, I grant you that if the CSV is a large file, then a StreamReader and the .split() method are probably not the most efficient approach, but the OP didn't indicate it was a large file. Other than the file size, there is no reason why reading the file contents in, escaping any trouble characters and parsing the string at the delimeter wouldn't work just fine. "GhostInAK" <paco(a)paco.net> wrote in message news:be1391bf1c5218c8bb6e9594ebe3(a)news.microsoft.com... > Hello Scott M., > > Well, yes, you could write your own CSV parser as MDO did.. but that would > serve no practical purpose other than to teach you how to write a string > parser. > > I assume when you said "parse at the comma" you meant string.split. While > you could use this function, it would be stupid to use it on a CSV file. > Quoted values are going to kill you. It's not worth it. > > -Boo > >> You can use this technique to parse the file at any character, it >> doesn't have to be the comma. >> >> "GhostInAK" <paco(a)paco.net> wrote in message >> news:be1391bf1c1ee8c8ba8d6a850af6(a)news.microsoft.com... >> >>> Hello Scott M., >>> >>> Because not all CSV files are supposed to be parsed at the comma: >>> Value One, "Value, Two", Value Three >>> >>> OP, your connection string is wrong. Try: >>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended >>> Properties=Text; >>> >>> -Boo >>> >>>> Why not just use a StreamReader class and parse the values at the >>>> commas? >>>> >>>> "fniles" <fniles(a)pfmail.com> wrote in message >>>> news:%23pruw%23K7GHA.4708(a)TK2MSFTNGP05.phx.gbl... >>>>> I have a .CSV file (comma delimited) that I want to open using >>>>> OLEDB, >>>>> but I >>>>> get the error "External table is not in the expected format." >>>>> If I save the .CSV file to an .XLS file, I can open the connection >>>>> with no >>>>> problem. >>>>> What is the correct way to open a .CSV file ? >>>>> If I can not open the CSV file, how can I programmatically save the >>>>> CSV >>>>> file to an XLS file ? >>>>> Thanks a lot. >>>>> dim myCon OleDb.OleDbConnection >>>>> myCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data >>>>> Source=c:\file.csv; Extended Properties=""Excel 8.0; HDR=NO; >>>>> IMEX=1""") >>>>> --> error "External table is not in the expected format." > > |