asked on Transferspreadsheet formatting issues
I am creating a procedure to import a spreadsheet into an Access 2013 for processing. Simple enough I've done it dozens of times.
I set up a table 'tblImport' to import the spreadsheet into with a column F1 - F12 to accept each field on the spreadsheet. Each column in this table is defined as text 50 with no 'Format' or 'InputMask' specified.
I use this code to import the spread into the tblImport
I attached a file showing the pertinent columns in the spread. Everything works as it should except for most of the columns that may contain a date or a text description, usually the word 'Hold'.
The first column shown in the attached pic works just as I would expect. The data in that column is imported into column 'F7' in tblImport. In column F7 in the datasheet view I see rows with a date and rows with the word 'Hold' in them just as I would expect.
The remaining columns, which are all defined as text 50 just like column F7, don't import the same way. Only the dates import, the text 'Hold' never does. It throws a type conversion error. When I look at those columns in the imported table I only see dates, nothing is in
the rows that had the word 'Hold' in the spreadsheet cell.
I don't understand why column F7 would import properly and the others don't. They are all defined exactly the same in the table.
It seems like something in the Transferspreadsheet is causing these columns to be looked at a type 'Date', even though I have them defined as text.
I'm pretty sure I ran into this several years ago when importing a spreadsheet and the solution was to add a fairly short and simple piece of VBA code prior to importing the spreadsheet that changed the format of each cell in the import range to something like 'General'. The VBA then save the spreadsheet prior to the 'Transferspreadsheet' logic being executed.
Unfortunately I don't have Access to the VBA code I created to do the reformatting.
Does anyone have a solution to the problem?
Perhaps the pre-import reformatting is the only way to go. In that case can anyone provide an example of the code needed?
SpreadPic.doc
I set up a table 'tblImport' to import the spreadsheet into with a column F1 - F12 to accept each field on the spreadsheet. Each column in this table is defined as text 50 with no 'Format' or 'InputMask' specified.
I use this code to import the spread into the tblImport
'
wkImportRange = "!A6:L8000"
'
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel8, _
inputTable, _
passedSpreadsheetNameAndLoc, _
False, _
wkImportRange
I attached a file showing the pertinent columns in the spread. Everything works as it should except for most of the columns that may contain a date or a text description, usually the word 'Hold'.
The first column shown in the attached pic works just as I would expect. The data in that column is imported into column 'F7' in tblImport. In column F7 in the datasheet view I see rows with a date and rows with the word 'Hold' in them just as I would expect.
The remaining columns, which are all defined as text 50 just like column F7, don't import the same way. Only the dates import, the text 'Hold' never does. It throws a type conversion error. When I look at those columns in the imported table I only see dates, nothing is in
the rows that had the word 'Hold' in the spreadsheet cell.
I don't understand why column F7 would import properly and the others don't. They are all defined exactly the same in the table.
It seems like something in the Transferspreadsheet is causing these columns to be looked at a type 'Date', even though I have them defined as text.
I'm pretty sure I ran into this several years ago when importing a spreadsheet and the solution was to add a fairly short and simple piece of VBA code prior to importing the spreadsheet that changed the format of each cell in the import range to something like 'General'. The VBA then save the spreadsheet prior to the 'Transferspreadsheet' logic being executed.
Unfortunately I don't have Access to the VBA code I created to do the reformatting.
Does anyone have a solution to the problem?
Perhaps the pre-import reformatting is the only way to go. In that case can anyone provide an example of the code needed?
SpreadPic.doc
Microsoft AccessMicrosoft Excel
Last Comment
mlcktmguy
ASKER
Thanks for the response it is appreciated. I see what you're getting at. If the first cell is text, the rest will import as text. If the first cell is a date the rest will try to import as a date, regardless of how I have them defined in the receiving table in my application.
I must not have explained this well in my original post but all of the columns show with dates may also have 'Hold' in any of the cells. So even if I make sure I presort column F7 in all of the spreadsheets coming I will still have an issue with any of the other columns in which a date appears prior to the ‘Hold’ text.
I should also mention the fact that there will 300 of these spreadsheets to process at a time. So whatever has to be done will have to be done in VBA code Doing it manually is not feasible.
Hopefully there is a solution.
By the way, I changed the spreadsheet type to 10, even though these are .xls spreadsheets but the result was the same.
I must not have explained this well in my original post but all of the columns show with dates may also have 'Hold' in any of the cells. So even if I make sure I presort column F7 in all of the spreadsheets coming I will still have an issue with any of the other columns in which a date appears prior to the ‘Hold’ text.
I should also mention the fact that there will 300 of these spreadsheets to process at a time. So whatever has to be done will have to be done in VBA code Doing it manually is not feasible.
Hopefully there is a solution.
By the way, I changed the spreadsheet type to 10, even though these are .xls spreadsheets but the result was the same.
are all of the 300 spreadsheet have the same format?
will they be imported to the same table?
one solution is to convert the excel file to .CSV
will they be imported to the same table?
one solution is to convert the excel file to .CSV
- import one file and create an import specification
To create import specification :
https://www.experts-exchange.com/Database/MS_Access/Q_28129404.html
To create import specification :
https://www.experts-exchange.com/Database/MS_Access/Q_28129404.html
- the rest of the .csv files can be imported using the command line
example : https://www.experts-exchange.com/Database/MS_Access/Q_28602198.html
example : https://www.experts-exchange.com/Database/MS_Access/Q_28602198.html
ASKER
Yes all the files are the same format but converting them all to CSV is not an option. I understand how CSV be would better and I have developed many sets of import specs. The frustrating part of importing spreadsheets is you can't do an import spec. In this situation I have to adapt to the customer requirements.
<Yes all the files are the same format but converting them all to CSV is not an option.>
WHY?
WHY?
ASKER
"In this situation I have to adapt to the customer requirements. "
I really don't understand...
anyway, you can still use vba codes
- open the table as recordset
- open the excel file and read each cell and add the records to the recordset.
anyway, you can still use vba codes
- open the table as recordset
- open the excel file and read each cell and add the records to the recordset.
- another option is to create link to the excel file
- create an append query using the link excel file to the destination table.
- create an append query using the link excel file to the destination table.
and I forgot,
-import the excel file using the command line that you are using now, but instead of directly appending to the table, use a tempTable
- transfer the records from the tempTable to the final table using an append query with fields formatted accordingly.
-import the excel file using the command line that you are using now, but instead of directly appending to the table, use a tempTable
- transfer the records from the tempTable to the final table using an append query with fields formatted accordingly.
ASKER
Thanks for your responses. I appreciate you taking an interest in my situation.
Of the suggestions you've given so far the one which seems most probable is "
- open the table as recordset
- open the excel file and read each cell and add the records to the recordset.
I can connect the spreadsheet to a recordset but don't know how to read each cell.
In the meantime I checked thru my old code and found the VBA loop that will alter the characteristics of specific cell in an Excel spreadsheet.
Here is the code that alters the formatting of columns F - P :
Using a modified version of this loop that alters the format of the cells to 'General' seems to be the most efficient option. Successfully altering all the cells to 'General' format will import the cells with 'Hold' and the cells with dates but the dates come in as numbers, '3456789' rather than '01/01/15'.
To use the above loop, I need to know how to revise it so instead of revising the .Numeric property of the cells I need to revise the cell formatting to 'General' . I don't know how to do that, do you?
I also need to know how to convert the dates imported as numbers '3456789' back into dates.
Any ideas?
Of the suggestions you've given so far the one which seems most probable is "
- open the table as recordset
- open the excel file and read each cell and add the records to the recordset.
I can connect the spreadsheet to a recordset but don't know how to read each cell.
In the meantime I checked thru my old code and found the VBA loop that will alter the characteristics of specific cell in an Excel spreadsheet.
Here is the code that alters the formatting of columns F - P :
Private Sub fixSpreadsheetNumFormat(passedNameAndLoc As String)
Dim xlApp As Object, xlWb As Object, xlWs As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(passedNameAndLoc)
Set xlWs = xlWb.Worksheets(1)
xlWs.Range("F:P").NumberFormat = "0"
'repeat as needed
With xlWb
.Save
.Close
End With
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
Using a modified version of this loop that alters the format of the cells to 'General' seems to be the most efficient option. Successfully altering all the cells to 'General' format will import the cells with 'Hold' and the cells with dates but the dates come in as numbers, '3456789' rather than '01/01/15'.
To use the above loop, I need to know how to revise it so instead of revising the .Numeric property of the cells I need to revise the cell formatting to 'General' . I don't know how to do that, do you?
I also need to know how to convert the dates imported as numbers '3456789' back into dates.
Any ideas?
You don't need automation code to do this. You can do it by appending to a local table. Define the table to have text fields. Then using the TransferSpreadsheet, specify append rather than import as the option.
You will need to delete the contents of this table prior to doing the TransferSpreadsheet. Also, keep in mind that this temp table will cause bloat so you will need to compact the database more frequently than you have been.
Dates are stored internally as double precision numbers. The integer part represents the number of days since the origin date (0 value). In Access, the 0 date is Dec 30, 1899. In Excel, I think it is Jan 1, 1900 but the concept is the same.
Use the append method and just import the dates as date strings. After you import to the temp table, you will need to append to the real table where I seriously hope that you have dates defined as DateTime and "hold" is a status flag which is a separate field.
You will need to delete the contents of this table prior to doing the TransferSpreadsheet. Also, keep in mind that this temp table will cause bloat so you will need to compact the database more frequently than you have been.
Dates are stored internally as double precision numbers. The integer part represents the number of days since the origin date (0 value). In Access, the 0 date is Dec 30, 1899. In Excel, I think it is Jan 1, 1900 but the concept is the same.
Use the append method and just import the dates as date strings. After you import to the temp table, you will need to append to the real table where I seriously hope that you have dates defined as DateTime and "hold" is a status flag which is a separate field.
ASKER
PatHartman: I don't see an 'Append' option on the Transferspreadsheet command. Only acExport, acImport and acLink.
How do I accomplish the append?
How do I accomplish the append?
ASKER
Rey Obrero: I appreciate all of your suggestions. I got pulled off this task for a few days.
As you suggested, I created a test that linked the spreadsheet to the DB as an input table. I read the table line by line and processed each cell individually.
This didn't work any better than the Tranferspreadsheet import. When I researched I saw that when the link was created the fields that contained dates were defined in the link table as 'Date/Time'. Since it's a linked table I cannot alter the data definitions and this caused issues when the rows with 'Hold" in those columns were read.
I still haven't been able to successfully import this spreadsheet. Any other ideas would be welcome.
I got some input from an EE'er and was able to revise the spreadsheet prior to import, chnaging all of the columns to format 'General'. This was still unsuccessfull with the columns containing 'Hold" not being imported.
Here's the revised code that reformats the spreadsheet prior to import
Any other ideas would be appreciated.
As you suggested, I created a test that linked the spreadsheet to the DB as an input table. I read the table line by line and processed each cell individually.
This didn't work any better than the Tranferspreadsheet import. When I researched I saw that when the link was created the fields that contained dates were defined in the link table as 'Date/Time'. Since it's a linked table I cannot alter the data definitions and this caused issues when the rows with 'Hold" in those columns were read.
I still haven't been able to successfully import this spreadsheet. Any other ideas would be welcome.
I got some input from an EE'er and was able to revise the spreadsheet prior to import, chnaging all of the columns to format 'General'. This was still unsuccessfull with the columns containing 'Hold" not being imported.
Here's the revised code that reformats the spreadsheet prior to import
Public Sub fixSpreadsheetToGeneralFormat(passedNameAndLoc As String)
Dim xlApp As Object, xlWb As Object, xlWs As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(passedNameAndLoc)
Set xlWs = xlWb.Worksheets(1)
'xlWs.Range("F:P").NumberFormat = "0"
xlWs.Range("A6:M" & xlWs.UsedRange.Rows.Count).NumberFormat = "General"
'repeat as needed
With xlWb
.Save
.Close
End With
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
Any other ideas would be appreciated.
can you upload a copy of one of the excel file.. remove sensitive info.
It is part of the External Data dialog.![Excel Append]()
ASKER
PatHartman: Thanks for showing where the append is located. However, there are 300 spreadsheets to be imported an processed so whatever I use has to be available in VB code.
I finally created and attached a scrubbed version of an example spreadsheet to be imported.
Here is the code I am using to import the data cells, which begin in row 6.
I also attached a word file with snapshots of the table definition of the table I am importing into and the result of the import.
The issues is shown in the resultant table. The cells in row F8 and F9 that contain the word 'Hold' on the imported spreadsheet are not being imported into the resultant table. For some reason the 'Hold' cells in row F7 and F10 are being imported.
I need all cells in all rows to be imported and the process must be doable in VB code. Thaks for your interest in my issue. Any help will be appreciated.
EETestVers.xls
EETableDefAndContents.doc
I finally created and attached a scrubbed version of an example spreadsheet to be imported.
Here is the code I am using to import the data cells, which begin in row 6.
'
wkImportRange = "!A6:L8000"
'
DoCmd.TransferSpreadsheet acImport, _
10, _
inputTable, _
passedSpreadsheetNameAndLoc, _
False, _
wkImportRange
I also attached a word file with snapshots of the table definition of the table I am importing into and the result of the import.
The issues is shown in the resultant table. The cells in row F8 and F9 that contain the word 'Hold' on the imported spreadsheet are not being imported into the resultant table. For some reason the 'Hold' cells in row F7 and F10 are being imported.
I need all cells in all rows to be imported and the process must be doable in VB code. Thaks for your interest in my issue. Any help will be appreciated.
EETestVers.xls
EETableDefAndContents.doc
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Wow, that is outstanding, I wish I could give you more than 500 points.
Just one requested revision to the logic: This statement:
.worksheets("active files").Select
pulls the data from the 'Active files' worksheet. Theoretically all of the spreadsheets being imported will have the first tab with the name 'Active files'. However, knowing that users can sometimes mis-spell or arbitrarily decide they don't like the name 'Active files':
Is there a way the above statement can be revised to always select the first tab on the worksheet. rather than 'Active Files' specifically. Most of the spreadsheets will come in with data on other tabs ( I removed it in the scrub) but the data I'm interested will always be on the first tab.
Thanks much
Just one requested revision to the logic: This statement:
.worksheets("active files").Select
pulls the data from the 'Active files' worksheet. Theoretically all of the spreadsheets being imported will have the first tab with the name 'Active files'. However, knowing that users can sometimes mis-spell or arbitrarily decide they don't like the name 'Active files':
Is there a way the above statement can be revised to always select the first tab on the worksheet. rather than 'Active Files' specifically. Most of the spreadsheets will come in with data on other tabs ( I removed it in the scrub) but the data I'm interested will always be on the first tab.
Thanks much
if it is always the first tab
replace
.worksheets("active files").Select
wirh
.worksheets(1).Select
replace
.worksheets("active files").Select
wirh
.worksheets(1).Select
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for mlcktmguy's comment #a40954912
for the following reason:
Perfect. Thank you so much for staying with my issue until it was resolved. You are definitely an all-star.
Accepted answer: 0 points for mlcktmguy's comment #a40954912
for the following reason:
Perfect. Thank you so much for staying with my issue until it was resolved. You are definitely an all-star.
@mlcktmguy
are you sure you want to accept your comment as the solution???
.
are you sure you want to accept your comment as the solution???
.
ASKER
Absolutely not, how do I undo my mistake?
2. replace your code with
DoCmd.TransferSpreadsheet acImport, _
10, _
inputTable, _
passedSpreadsheetNameAndLo
False, _
wkImportRange