Click here to Skip to main content
15,918,742 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi i have this code:
SQL
Dim query As String = "INSERT INTO  SEGDATA (School, Campus, AdminNo, ModuleCode, ModuleGrp) " & _
      "SELECT F1 as School, F2 as Campus, F3 as AdminNo, F4 as ModuleCode, F5 as ModuleGrp FROM [Text;DATABASE=" & System.IO.Path.GetDirectoryName(Me.OpenFileDialog1.FileName) & ";HDR=NO].[" & Me.OpenFileDialog1.SafeFileName & "]"



Which it works fine inserting comma delimited file into the access database, but i wanna insert TAB delimited file, how can i edit this?

Thanks in advance!
Posted

1 solution

 
Share this answer
 
Comments
12345_abcde 10-Jul-13 22:45pm    
i edited my connection string to

connect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\segdata.accdb" & _
"Extended Properties='text;FMT=TabDelimited;HDR=YES'"

i received this error:

Format of the initialization string does not conform to specification starting at index 161.

why is that so? it seems like it doesn't recognizes the format of the connection string?
berrymaria 10-Jul-13 23:14pm    
Could you change your data source? I tried to replicate, and add mine FMT=TabDelimited but it doesn't return any error.

Below is my example, but then the extended properties is not 'text.

string strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Employees.xlsx;Extended Properties=""Excel 12.0 XML;FMT=TabDelimited;HDR=Yes;IMEX=1""";

or try to put a semicolon before Extended Properties in your connection.

connect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\segdata.accdb" & _
";Extended Properties='text;FMT=TabDelimited;HDR=YES'"
12345_abcde 11-Jul-13 0:43am    
i add in the extended propeties, it prompt me the error wrong path name, without the properties, it worked fine if is csv file.
berrymaria 11-Jul-13 2:23am    
See this link:

http://httpapplication.blogspot.com/2010/07/oledbconnection-string-for-excel-csv.html

Tab Delimited
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePathWithoutFileName;Extended Properties='text;HDR=YES;FMT=TabDelimited'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900