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.
| Author |
Topic |
|
gaos
Starting Member
6 Posts |
 Posted - 2005-09-15 : 19:57:45
|
| I use dts loading data from excel to sql db, and I had a missing data problem. If the excel field contains mixed data types such as 1234, 2345 and abcd and the field is formated as GENERAL (default), then the data abcd is missing when loading with dts. If I format the field with the mixed data already there as text, the data in column look as text, but it still missing data when loading. However, if i format the field as text first, then enter the same mixed data, it is ok to load all the data. Does anyone have similar problem or solutions?Thanks,John |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-09-17 : 14:27:39
|
| I think its because excel determines the column data type based on the first 8 or so records of the column, which ever row of that column doesnt fit that data type it wont upload any value for that row. I had a similar problem, what I did is I changed the format of the specific column in my dts activex script.Karunakaran |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-18 : 02:47:50
|
| I think my reply to this was lost when the site was restored from backup on Friday night.gaos: If you didn't see my reply post a follow-up here and I will rewrite it as best I canKristen |
|
|
|
gaos
Starting Member
6 Posts |
Posted - 2005-09-22 : 15:13:56
|
| Kristen, thank you for your feed back again. I saw and read your first replay and I agree with your comment: should avoid using Excel as data source to load data, but end users like Excel.Karunakaran,I am using VB script to convert the excel files to text files first, then load data from the text files. I tried to use Excel object and ActiveWorkbook.SaveAS Filename="TextFilePath & TextFileName", FileFormat=xlText, CreateBackup=False. However, constant xltext did not work, and the files were not saved as the text file with tab delimmited as I wanted. Any ones know what is the constant for tab delimited text file for ActiveWorkbook.SaveAS? I have tried xlCSV, xlTextWindows, xlUnicodeText, but none is working neither. |
|
|
|
|
|
|
|
|
missing data when loading from Excel