Frothingslosh
Premier Pale Stale Ale
- Local time
- Today, 00:49
- Joined
- Oct 17, 2012
- Messages
- 3,276
Here's the situation:
I have set up a raw data table named "dbo_tblRawData". It has 26 fields (way more than the number of columns I'll ever have to import into this tool), all of which are 100-character text fields.
I am trying to use DoCmd.Transferspreadsheet to transfer a spreadsheet into this table. The spreadsheet has 15 columns. I'm using a defined range that pulls in everything except the header row.
Here's the catch: Column F (Maximum Fee) is a mixed-format column. 99% of the values are currency, but it also contains values of "M", which I process later. Every time I attempt to import it, I get a numeric field overflow, and I've narrowed down the culprit to the Max Fee column.
Permanent changes aren't allowed (the original data must be saved unchanged), but I have attempted converting the number formats for the entire spreadsheet to Text for the duration of the import, and encountered the same error. I've even tried loading a temporary copy that I had converted to text and saved, but encountered the same error.
I know that the overflow error happens when you attempt to load a text character into a numeric field, but this is a text field I'm trying to load data into.
My restriction here is that I have to load the spreadsheet as-is. (It's a health insurance fee schedule from my state's department of community health.) Any changes I make cannot be saved.
Here's the appropriate code snippet:
DataImport is a custom class that basically stores a ton of metadata I need for the full process. The function works just fine as long as there is no text in the Fee column. :banghead:
Does anyone have any suggestions on how to work around this? I'll make the code build a custom table if I have to, but I *REALLY* don't like that idea.
I have set up a raw data table named "dbo_tblRawData". It has 26 fields (way more than the number of columns I'll ever have to import into this tool), all of which are 100-character text fields.
I am trying to use DoCmd.Transferspreadsheet to transfer a spreadsheet into this table. The spreadsheet has 15 columns. I'm using a defined range that pulls in everything except the header row.
Here's the catch: Column F (Maximum Fee) is a mixed-format column. 99% of the values are currency, but it also contains values of "M", which I process later. Every time I attempt to import it, I get a numeric field overflow, and I've narrowed down the culprit to the Max Fee column.
Permanent changes aren't allowed (the original data must be saved unchanged), but I have attempted converting the number formats for the entire spreadsheet to Text for the duration of the import, and encountered the same error. I've even tried loading a temporary copy that I had converted to text and saved, but encountered the same error.
I know that the overflow error happens when you attempt to load a text character into a numeric field, but this is a text field I'm trying to load data into.
My restriction here is that I have to load the spreadsheet as-is. (It's a health insurance fee schedule from my state's department of community health.) Any changes I make cannot be saved.
Here's the appropriate code snippet:
Code:
'Assign SourceFile to the source being checked on this iteration of x.
Select Case x
Case 1
Set SourceFile = DataImport.wb1
Case 2
Set SourceFile = DataImport.wb2
Case 3
Set SourceFile = DataImport.wb3
End Select
'Determine the number of rows that will be entered.
NumberOfRows = SourceFile.Sheets(1).UsedRange.Rows.Count
'Make sure that dbo_tblRawData is empty.
SQL = "DELETE * FROM dbo_tblRawData;"
CurrentDb().Execute SQL, dbFailOnError
'Determine the range to be transferred.
Range = DataImport.FirstDataRow & ":" & NumberOfRows
'Convert the used columns to text.
SourceFile.Sheets(1).Range(Range).NumberFormat = "Text"
'Transfer the data from the current source to dbo_tblRawData
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo_tblRawData", _
SourceFile.Path & "\" & SourceFile.Name, False, RangeDataImport is a custom class that basically stores a ton of metadata I need for the full process. The function works just fine as long as there is no text in the Fee column. :banghead:
Does anyone have any suggestions on how to work around this? I'll make the code build a custom table if I have to, but I *REALLY* don't like that idea.
