Field Truncation using connection string sql statement

[sxschech]

Been using something like this to import certain data elements from a single col excel extract file, all was well as the data I was pulling from the query and parsing was either numeric or under 100 chars. Recently, it was requested to include some comments. The comments are being truncated.

stsql = "SELECT F1 " & _
        "FROM [Excel 12.0;HDR=NO;IMEX=1;Database=" & PathFile & "].[" & sheetname & "$A1:A10000] AS T1 " & _
        "WHERE F1 Is Not Null"

If the above is not able to handle, what are the alternatives?

I looked at

http://www.tek-tips.com/viewthread.cfm?qid=1332813.

I tried it out and it seems to be able to pull in the entire comment. Here is the issue; if I use this method, I won't know the row number up front. I would need a mechanism to search the excel sheet for the cell containing the value because the cell row containing the comment will vary from sheet to sheet. Because the file contains a comment field for more than one question, I need to do a two pass find, first to find the phrase "Please comment on X..." then find the actual cell which starts with the word "Responses".

 

[lameid]

Personally, I do not use embedded connections in queries so I don't have much experience there...

A problem with Excel in general is that the column data types are not strongly typed. I am guessing that Access is considering you comments column as 255 text instead of a memo. Often moving the bigger or obvious text data in a bunch of numeric data helps.

You can specify data types when linking a text file, so saving to a text file and connecting that way would give you use of the data. Data is often in Excel for a reason so this may not be a practical option but may at least let you get through things as a one off if it is urgent.

If it were me I would probably use automation to have excel save the data to text (CSV or otherwise) and then import or link that into access. You may be able to use the same query connection but again unless you specify the data types while linking, Access will be guessing and likely still get it wrong.

 

[sxschech]

I'm at my other job today, but to see if I understand, since the excel file is a single col, I should create a single col table in access and make it a memo field, then link or import the data to that. From there run my query as a recordset and parse the results into an actual usable table. If I save each sheet into a text file, that is going to be a lot of files to keep track of as one particular file has 250 sheets and there are six of these files at present.

 

[sxschech]

lameid, was able to work on this today. I saved a sheet to csv, linked it in and saved the specification with Long Text. I figured out via access vba, how to export a sheet from excel one at a time, save it to csv, link it into access, then after it is processed, delete the file (keep the link), go back and get the next sheet and repeat. In this way, I don't have hundreds of csv files to worry about.