הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Sunday, August 11, 2013 10:01 PM | 1 vote
("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Settings.locationTable & ";Extended Properties='Excel 12.0;IMEX=1;ImportMixedTypes=Text;HDR=NO;';")
That is the connection string I am using to import an xlsx or xls file into a datagridview in visual studio 2010. I have tried moving the extended properties around to see if the order matters but have had no luck.
If I have HDR=YES, the leading zeroes on the Zip Code column fall off and any Text in a column that also contains numbers is turned to null.
If I have HDR=NO, all data imports properly but I do not have a header column to identify anything with. My first row still contains my data for the headers but headers become f1,f2,f3 etc.
Please help!
I have screenshots but cannot post them due to my account not being verified.
All replies (3)
Monday, August 12, 2013 12:58 PM ✅Answered
Hi Chris,
According to your description, i've done some tests, as you said, if HDR=NO, all data imports properly but no header column.
There is a similar thread:
#Import leading zero from a excel file into a datagrid view
http://social.msdn.microsoft.com/Forums/windows/en-US/7014b976-68c9-4557-89d0-4b7e2a336b9b/import-leading-zero-from-a-excel-file-into-a-datagrid-view
Then i try my best to find a way to solve this issus, there are some solutions, please check below:
#Excel, OleDb, and Leading zeros
http://stackoverflow.com/questions/438099/excel-oledb-and-leading-zeros
#Import Excel to Datagrid without losing leading zeros
http://social.msdn.microsoft.com/Forums/windows/en-US/a6ddb5e3-1001-4a0a-844b-156471afd721/import-excel-to-datagrid-without-losing-leading-zeros
Franklin Chen
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Monday, August 12, 2013 1:05 PM ✅Answered
Hello,
A work around if you know how many columns you are extracting is to have your SQL alias columns. So let's say column A is "first name", column B is "last name", C is Address, D City, E State, F Zip code. With HDR=No you get
F1, F2, F3, F4, F5, F6
Now if we did the following you would have headers (I left off the FROM)
SELECT F1 As FirstName, F2 As LastName, F3 As Street, F4 As City, F5 As State, F6 As ZipCode
Of course if you don't know which column holds specific data the above idea will not work for you yet is worth knowing.
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.
Monday, August 12, 2013 2:19 PM ✅Answered
Another option:
Using data from the first row, loop through the datatable's column collection and set the column names, then delete the first row.
Sample code:
Dim ix As Integer = 0
For Each col as datacolumn in dt.columns
col.ColumnName = dt.rows(0).item(ix)
ix += 1
Next
dt.rows(0).Delete
The difference between genius and stupidity is that genius has its limits.