Mapping Excel fields when linking to Access app.

You may have to use automation - in Access to open an Excel instance, open the workbook, and read the values of the range in question directly.

You can also predefine the field types and lengths in the Access table, and then, instead of simply linking the Excel file, you can use TransferSpreadsheet method to insert the values from Excel into Access.  This gives you a bit more flexibility in the process.

Dale

Another solution might be to export the Excel sheet to a CSV file and then import it. I have had trouble in the past with Excel because sometimes there are hidden fields on the Spreadsheet which causes problems while linking. Once you create the CSV file you can examine it fro consistency.

Hi Experts,

The following is the query used to import the data from that linked Excel (V_Visit_Note_Export) sheet into my Access table (Skilled_Nursing_Visit_Note).

Can someone post the code will have to use instead, when using those methods above?

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID, Client_Last_Name, Visit_Date, Date_Of_Birth, Medical_Record_Number, Shift_From_Hour, Shift_To_Hour, Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, Vent_BIPAP_Settings, Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, Neuro_Notes, GI_Oral_Tube_Feeding, Current_Feeds, Gastric_Residuals_Present, Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, Number_Of_Diaper_Changes, Urine_Stool_Volume_Description, Bedbound, WheelChair, Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, Pain_Notes, Medication_Changes, Medication_Changes_2, Integument_Skin_Assessment, Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, Patient_Response, Instructions, Understanding, Plan_Goal, Nurse_Signature_Last_Name, Signature, Date_Signed, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, Reviewed_Status, Reviewed_By, Reviewed_stamp, Client_First_Name, Nurse_Signature_First_Name, Shift_From_Minute, Shift_To_Minute, Patient_Unable_To_Sign, Reason, Status )
SELECT V_Visit_Note_Export.SNV_ID, V_Visit_Note_Export.Client_Last_Name, V_Visit_Note_Export.Visit_Date, V_Visit_Note_Export.Date_Of_Birth, V_Visit_Note_Export.Medical_Record_Number, V_Visit_Note_Export.Shift_From_Hour, V_Visit_Note_Export.Shift_To_Hour, V_Visit_Note_Export.Purpose_Of_Visit_Goal, V_Visit_Note_Export.Temp, V_Visit_Note_Export.Pulse, V_Visit_Note_Export.RR, V_Visit_Note_Export.BP, V_Visit_Note_Export.O2_Saturation, V_Visit_Note_Export.O2_In_Use, V_Visit_Note_Export.Respiratory_Lung_Sounds, V_Visit_Note_Export.Respiratory_Breathing_Pattern, V_Visit_Note_Export.Bipap, V_Visit_Note_Export.Cpap, V_Visit_Note_Export.Vent, V_Visit_Note_Export.Vent_BIPAP_Settings, V_Visit_Note_Export.Cardiovascular_Skin_Color, V_Visit_Note_Export.Skin_Temp, V_Visit_Note_Export.Cardiovascular_Edema, V_Visit_Note_Export.Neuro_Alert, V_Visit_Note_Export.Neuro_Oriented, V_Visit_Note_Export.Neuro_Self_Directing, V_Visit_Note_Export.Neuro_Dev_Delay, V_Visit_Note_Export.Neuro_Deficits, V_Visit_Note_Export.Neuro_Notes, V_Visit_Note_Export.GI_Oral_Tube_Feeding, V_Visit_Note_Export.Current_Feeds, V_Visit_Note_Export.Gastric_Residuals_Present, V_Visit_Note_Export.Residual_Volume, V_Visit_Note_Export.MD_Called, V_Visit_Note_Export.Abdominal_Assessment, V_Visit_Note_Export.Feeding_Tolerance, V_Visit_Note_Export.GU_Incontinent, V_Visit_Note_Export.Number_Of_Diaper_Changes, V_Visit_Note_Export.Urine_Stool_Volume_Description, V_Visit_Note_Export.Bedbound, V_Visit_Note_Export.WheelChair, V_Visit_Note_Export.Ambulatory, V_Visit_Note_Export.Mobility_Aids, V_Visit_Note_Export.Contractures, V_Visit_Note_Export.Musculoskeletal_Notes, V_Visit_Note_Export.Pain_Score, V_Visit_Note_Export.Pain_Notes, V_Visit_Note_Export.Medication_Changes, V_Visit_Note_Export.Medication_Changes_2, V_Visit_Note_Export.Integument_Skin_Assessment, V_Visit_Note_Export.Skin_Care_Measures_Taken, V_Visit_Note_Export.Home_Environment_Safety, V_Visit_Note_Export.Treatments_Administered, V_Visit_Note_Export.Patient_Response, V_Visit_Note_Export.Instructions, V_Visit_Note_Export.Understanding, V_Visit_Note_Export.Plan_Goal, V_Visit_Note_Export.Nurse_Signature_Last_Name, V_Visit_Note_Export.Signature, V_Visit_Note_Export.Date_Signed, V_Visit_Note_Export.Nurse_Name_Stamp_SNV, V_Visit_Note_Export.Nurse_User_ID_num_SNV, V_Visit_Note_Export.Medication_Changes_Yes, V_Visit_Note_Export.Acknowledge_Accuracy, V_Visit_Note_Export.Trach, V_Visit_Note_Export.CCM_Informed, V_Visit_Note_Export.Tube_Type, V_Visit_Note_Export.Reviewed_Status, V_Visit_Note_Export.Reviewed_By, V_Visit_Note_Export.Reviewed_stamp, V_Visit_Note_Export.Client_First_Name, V_Visit_Note_Export.Nurse_Signature_First_Name, V_Visit_Note_Export.Shift_From_Minute, V_Visit_Note_Export.Shift_To_Minute, V_Visit_Note_Export.Patient_Unable_To_Sign, V_Visit_Note_Export.Reason, V_Visit_Note_Export.Status
FROM Skilled_Nursing_Visit_Note RIGHT JOIN V_Visit_Note_Export ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
WHERE (((V_Visit_Note_Export.Status) Is Null Or (V_Visit_Note_Export.Status)<>"draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null)) OR (((V_Visit_Note_Export.Status)="draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null) AND ((V_Visit_Note_Export.Deleted)=True));

Select allOpen in new window

Thanks,
Ben

I realized that linking to CSV does not have that limitation, there I can specify the datatypes (see attached).

Now I have an option of downloading these files in CSV format instead, however would need your help on how to modify the code below to accommodate this change...

    sExt = "XLSX"
    
    sDestinationFolder = "C:\Application\"
       sDownloadFolder = "F:\Conversion\Caspio\"
    sBackupFolder = "E:\CaspioBackups\"
 
    
    sLastFile = "CaspioTables.zip"
    i = UnZip(sDownloadFolder & sLastFile, sDestinationFolder, False)
    
    If i = 0 Then
        sDestinationFolder = "C:\Application\"
        
        If Dir(sDestinationFolder & NewestFile(sDestinationFolder, "*." & sExt)) <> "" Then
            If Dir(sDestinationFolder & "Tables." & sExt) <> "" Then
                Kill sDestinationFolder & "Tables." & sExt
            End If
            Name sDestinationFolder & NewestFile(sDestinationFolder, "*." & sExt) As sDestinationFolder & "Tables." & sExt
        End If
    End If

Select allOpen in new window

Currently the Excel option downloads all sheets into one spreadsheets divided by sheets.

However the CSV option will save two files into one zip file named "C:\Users\bfuchs\Downloads\Tables_2018-Nov-29_1738.zip"

So that means after unzipping those files I have to rename them (removing the date/time from the file names), see 2nd attachment.

Actually this was the reason I didn't went with the CSV option to begin with...

How can I accomplish that?

Thanks,
Ben
Untitled.png
Untitled.png

Ben,

As much as I prefer using CSV files, I've found that many of these have commas embedded within quoted text strings, and Access does not handle these well (neither does SQL Server for that matter).  You might want to take a look at the article I wrote on Reading Wide CSV Files.  This was specifically about reading CSV files with > 255 columns, but ended up being more about parsing CSV files and eliminating the embedded commas within text strings.

@Dale,

As much as I prefer using CSV files, I've found that many of these have commas embedded within quoted text strings, and Access does not handle these well

In my scenario it looks like its handling fine all columns including commas (see attached).
Therefore if I find a way to rename those files by code (as mentioned above) I would be happy...
Thanks,
Ben
Untitled.png

As Dale suggested, simply import the spreadsheet.

If you go to External Data, New Datasource, From File, Excel, and go through the Wizard, you can specify the column data types

They have to be accurate because Access will complain about "row being too long / large"

You can specify table name to create for the import - make it something unique like  "V_Visit_Note_Export_Staging"

If need be, you can go back (after the event) and further refine the column sizes in Design View of the Table.

But that is just getting it into Access.

You still have data types to comply with in getting the INSERT resolved (go from Access to SQL Server)

There is no silver bullet, just elbow grease, some hair pulling, frustration and ultimately success.

Can we now go back to your other thread and finish off that work ?

Hi Mark,

If you go to External Data, New Datasource, From File, Excel, and go through the Wizard, you can specify the column data types

Actually depends how you want do it, if you use the wizard to import, then yes you get that option to select datatype etc...
However unfortunate I cannot do that within my Access app, as this will blow up my entire app,

I need a way to link it and perform the insert operation from one external data source to the other (currently from Excel/CSV to Access, and eventually from Excel/CSV to SQL Server).

Can we now go back to your other thread and finish off that work ?

As mentioned in the other thread, I cannot resume that until this gets resolved, especially that we're not sure yet which file type we are going to end up using, and it maight very well be that by working with CSV, we are going to face different issues (happen to be I tested that earlier today, CSV->SQL and got different error message...).

Don't worry, we are going get to that too, sooner or later-:)

Thanks,
Ben

@Ben,

That query you posted above is inserting into Skilled_Nursing_Visit_Note from V_Visit_Note_Export

How can that be when you cannot import your Spreadsheet into V_Visit_Note_Export ?

You need to worry about the definition of the table / data you are importing from / to to make sure there is no truncation.

Which columns are being truncated ?

Or, is this yet another different approach to try to work around the problems of using Access to update/insert a SQL Server Table ? Hence posting that particular query ?

Hi,

That query you posted above is inserting into Skilled_Nursing_Visit_Note from V_Visit_Note_Export

Right.

How can that be when you cannot import your Spreadsheet into V_Visit_Note_Export ?

Where do you see that?
V_Visit_Note_Export is the name of my Access linked table which is linked to a spreadsheet.
Currently the insert query works but fields are getting truncated.
See attached how are fields defined after I finish linking, all text are short text regardless of how much data they indeed contain in Excel.
Perhaps this is an Access bug...
Thanks,
Ben
Untitled.png

have you tried defining the fields in a staging table, and defining some of the fields (the long ones) as memo, and then, instead of linking to the spreadsheet, you import it into the staging table?

I believe I recommended this in one of my previous posts.

Hi,

instead of linking to the spreadsheet, you import it into the staging table?

Well for that will have to write code that reads a spreadsheet and perform this task, while currently I just have a link table and only running some queries to transfer from one source to the other...
I believe linking to CSV can solve this issue, just a matter of mapping to the right datatype, currently working in that direction, will keep you posted.

btw, I got a way to rename those CSV files (https://www.experts-exchange.com/questions/29127862/Remove-timestamp-from-file-name.html?anchor=a42746536¬ificationFollowed=219124928&anchorAnswerId=42746536#a42746536), therefore I believe is just a matter of time till I get this up and running...

Thanks,
Ben

I honestly thought you were importing.....

Yes, Excel as a Linked Table will only show the first 255 characters. That is a known limitation of linking directly to Excel.

See : https://support.office.com/en-us/article/import-or-link-to-data-in-an-excel-workbook-a1952878-7c58-47b1-893d-e084913cc958#__link_to_data  and scroll down to the grey chart and look for truncated values.

Also in that same article, there is a section on using the Wizard to import data and it clearly states (step 4) :

Access reviews the first eight rows in each column to suggest the data type for the corresponding field. If the column in the worksheet contains different types of values, such as text and numbers, in the first eight rows of a column, the wizard suggests a data type that is compatible with all the values in the column — most often, the text data type.

Which is why I posted above suggesting you use the Wizard and override the data types. However, I think you really need MEMO as Dale says above. There can still be issues with longtext.

Not sure where or when I had the thought that you were importing the data, but clearly had that impression....

Hi,

Yes, Excel as a Linked Table will only show the first 255 characters.

If only they would prompt us with a message (while starting the wizard of linking) informing that...why should someone think of this...(some software vendors put this kind of limitations under the evaluation license...at least they are so kind of informing you this in the first place), and my users were working with truncated data...-:(

Which is why I posted above suggesting you use the Wizard and override the data types.

FYI-There is no such an option when linking to an Excel file (XLSX).

Actually I ended up using CSV, where I was able to select correct datatypes.
Was stuck with some side issue regarding the use of CSV file (as posted above), however got pass that as well..
https://www.experts-exchange.com/questions/29127862/Remove-timestamp-from-file-name.html?anchor=a42748043¬ificationFollowed=219255602&anchorAnswerId=42748043#a42748043

Therefore just waiting for users confirmation in order to close this thread.

Thanks,
Ben

>> FYI-There is no such an option when linking to an Excel file (XLSX).

Yes, I know. That comment was in reference to Importing the data, not linking to Excel.

>> Actually I ended up using CSV, where I was able to select correct datatypes.

Would like to see the data definition  :)

@Mark,
See attached.
FYI- This is from the latest CSV I'm dealing with for now...
Thanks,
Ben
1.txt

Hi Experts,

While trying to do the above, I have fallen into another trap...please rescue me!!

https://www.experts-exchange.com/questions/29128549/Error-running-update-query-after-changing-from-XLSX-to-CSV.html#questionAdd

Thanks,
Ben

Hi Experts,

Access reviews the first eight rows in each column to suggest the data type for the corresponding field.

Currently I am stuck with this project, as whichever I turn there are heavy bumps on the road...

Since CSV turns to be problematic as well, I thought of an idea to stay with Excel and finding a way to force the wizard to recognize those long fields as "long text' as follows.

Will create a column named Sort by (yes/no) and then add some dummy records there with long text on those columns I need to be mapped as long text, and have this sort by that column...

What do you say for that?

Thanks,
Ben

Ben,

I know I mentioned this at least once in this or one of the other threads on this topic.  Have you created a staging table that you can import the data into, rather than linking it?  If you do this, you can define the fields with the appropriate data types or define them all strings except for those that need to be memo?

If you create the staging table with field names identical to those in the import file and fields defined as the appropriate data type, you set yourself up for potential import errors (if someone has entered a string in a numeric field) in Excel.  But if you setup all of those fields as strings, except those that need to be memo, then all of the data will import into this staging table.  When I do this, I generally add several fields to the end of the table (AN - Autonumber, Flag - Y/N, ImportComments-text).  Since these are at the end of the table, when the the file loads, the autonumber fills in appropriately, and the other remain blank or null (configure them the way you want).

This allows me to run a series of queries against the data in the staging table to determine whether all of the data is of the appropriate type (and also value for those fields that belong to lookup tables).  I run through this set of queries setting the Flag to True and the Comments to some Description for each error found.  Then I present those errors to the user to fix (or allow them to simply print a report so that they can fix the data in the Excel file).  Finally, when I am done with that I provide a way for the user to import all of the data into the appropriate fields in the production table, using the appropriate conversion function to convert the string value to numeric or other data types.

HTH
Dale

Hi Dale,

Have you created a staging table that you can import the data into, rather than linking it?

Currently we have data stored on the web (under Caspio) and there we have many tables, I only import them into Access in order to printout a report and keep track which were already printed.

The existing process is very simple, Caspio exports them to a variety of formats (Access, Excel, CSV and XML), then we have links to those exported files as tables, and running append queries will store them into local Access tables.

If I have to change the process and start reading those files by code, it will take significant programming time.

Therefore would prefer a solution that will keep the process flow as is, just fix those issues.

I understand there are other advantages of using the staging table, however can only work on issues we are currently facing (for the time being), perhaps at a later time will get to enhance this as well.

Therefore would focus primary on being able to overcome the Excel 255 limitation.

Thanks,
Ben

Hi Experts,

While trying to work on the above solution, to export to Excel some records (at least one record) with large data, which will force Access to map those columns as long text, I'm facing the following obstacle...

There is no way I can select the order of the records to get exported.

Therefore I have the following question, does someone know of a way to change the order of an Excel sheet by given column (thru code)?

Thanks,
Ben

Hi Experts,

At this point I think the best (easiest) way to go here is to avoid linking to Excel, just use CSV as linking file, and there a have a way of mapping it, while for the other issue I had with CSV, that cannot run update queries against it, I will have to delete those records and re-import, thereby avoiding having to use any update query...

Thanks,
Ben

Well i encountered this issue lately similar so here is what i found:
Lets assume that you have a Column named Status and you have in the cell
Status
1
2
3
Unknown
4
Unverified

When you link the Excel to Access you would probably think that Access would read the column and imports it as text...wrong...Access will read the 1st cell  --> 1 ( i assume the 1st row is treated as Field) and decides that this is a numeric field despite the text in the cells below...(it would just display an Error placeholder...i can't remember what it was).
So in your case ...if...its a quick and dirty solution...(probably too dirty) ...if you pad your cells (the 1st which contains the data ) with lets say 255 spaces ..or dots...or whatever...Access will read the Cell and decides its a long text....just tested it and its working...

@John,
Right that will work, however I cant use it as this will require either users change that file manually every time we get a new file (a couple of times per day...) or I enter new line by code (which I've mentioned above similar approach, of sorting by code.
Thanks,
Ben

I faced the same kind of an issue with EXCEL files.
I exported each file to CSV, then imported the text file - each line as one text record
                     Line Input #1, Temp                      
                     Names = Split(Temp, ",")
Names is an array containing each field of my data. I can now examine each element of Names and process it.