Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Field Truncation using connection string sql statement

Field Truncation using connection string sql statement

Field Truncation using connection string sql statement

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.

CODE -->

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".

RE: Field Truncation using connection string sql statement

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.

RE: Field Truncation using connection string sql statement

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.

RE: Field Truncation using connection string sql statement

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close