Data import from Excel using DTS

[edmundo]

Hi,

I'm trying to import data from an Excel spreadsheet.One of the fields in the spreadsheet is a comment field which is often greater than 255 characters.

On importing the data to SQL, this field concatenates at 255 chars. It doesn't matter whether I create the import table up-front with the field data type as text or whether I create a new table on import, the same happens.

Even if I create a tab-delimited text file from the spreadsheet, I get a row of #'s if the field is > 255 chars so it looks the same would happen with BCP.

I was wondering if anyone could shed any light as to how I could perform this data transfer effectively..

ta

Eddie

 

[foxdev]

Eddie, I had the exact same problem and, after a few hours of trying various workarounds, I gave up and created a VFP process to do it. At first I thought it was an Excel limitation, but I think its a DTS problem. After that episode, I now try to avoid using spreadsheets as a data transfer medium unless I must.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[edmundo]

just some information for anyone reading this thread..

I have managed to import the data above. I essentially exported the data from excel to a tab-delimited text file and used the bulk insert command to import the data. (I did have some problems - tab characters within the excel spreadsheet mainly)..

However, I use an access97 GUI and link to the SQL tables. Even though I have stored the data correctly in SQL, displaying it in Access is a real pain. It again concatenates at 255 characters by just linking the table

Workaround: Major fudge but the only way I can display the information in Access is:
1) to split the comments fields into numerous fields no greater than 255 in SQL
2) create a stored procedure
create procedure sp_siteaccess as
select * from tblsiteaccess
3) create a linked query in Access q_siteaccess linking to sp_siteaccess
4) create another query in Access rejoining the columns..
yuck

[An oddity of this is that if you merely create a joined query without splitting the fields (i.e. fields >255), acess just displays every character after the 255th!!]

the moral is : avoid this at all costs, unfortunately I can't...