By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Importing a CSV file? Erroring out

100+
P: 133
The line below codes the vba code that keeps erroring out with a "[31519]: You cannot import this file" message. According to the Access 2003 VBA Language Reference, TransferText Method, the syntax is used correct. expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage.

Expand|Select|Wrap|Line Numbers
  1. expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage.
  2.  
below is my code:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acImportDelim, "Credit_Memo", "L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.csv", True
  2.  
Any suggestions would be appreciated.
Mar 28 '11 #1

✓ answered by TheSmileyCoder

The importspec is the details of how the import is meant to run. For instance, in the DoCmd.Transfertext, you have specified that there is a delimiter, but you have not specified what the delimiter is! Such a thing among others are specified in an import spec. If you manually import the file the first time, you can specify such settings, and then save the import spec.

Share this Question
Share on Google+
8 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Does the import work when you do it manually?
Mar 28 '11 #2

100+
P: 133
yes, i can import it in access as a csv file.
Mar 31 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
You seem to be missing an argument in your syntax. ITs either the specification name or the table name.
Apr 1 '11 #4

100+
P: 133
i can't seem to locate the error. the credit_memo and the path file appear to be intact.

for time efficiency, the statement was converted into an excel import statement.

however, i welcome any suggestions.
Apr 5 '11 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Did you fix the missing argument?
Expand|Select|Wrap|Line Numbers
  1.     DoCmd.TransferText _
  2.         transferType:=acImportDelim, _
  3.         SpecificationName:="Credit_Memo", _
  4.         TableName:="", _
  5.         FileName:="L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.csv", _
  6.         HasFieldNames:=True
From your code, I can't guess whether Credit_Memo is your table name or the name of your importspec, but one or the other is missing. I have typed it out differently for you, which may make it more clear what is happening.
Apr 5 '11 #6

100+
P: 133
i'm having difficultly understanding what an import spec is? the credit_memo is the name of the access table. The following is the vba procedure in full, line 48 is where the executable statement is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ImportRecords()
  2.  
  3. On Error GoTo Err_Hndlr
  4.  
  5.  
  6.     If TableExists("SO_Data") Then
  7.     ' delete the table if it exists
  8.         DoCmd.DeleteObject acTable, "SO_Data"
  9.     End If
  10.     If TableExists("SO_Data$_ImportErrors") Then
  11.     ' delete the table if it exists
  12.         DoCmd.DeleteObject acTable, "SO_Data$_ImportErrors"
  13.     End If
  14.  
  15.  
  16.     If TableExists("SO_CFHeader") Then
  17.     ' delete the table if it exists
  18.         DoCmd.DeleteObject acTable, "SO_CFHeader"
  19.     End If
  20.     If TableExists("SO_CFHeader$_ImportErrors") Then
  21.     ' delete the table if it exists
  22.         DoCmd.DeleteObject acTable, "SO_CFHeader$_ImportErrors"
  23.     End If
  24.  
  25.     If TableExists("Credit_Memo") Then
  26.     ' delete the table if it exists
  27.         DoCmd.DeleteObject acTable, "Credit_Memo"
  28.     End If
  29.     If TableExists("Credit_Memo$_ImportErrors") Then
  30.     ' delete the table if it exists
  31.         DoCmd.DeleteObject acTable, "Credit_Memo$_ImportErrors"
  32.     End If
  33.  
  34.  
  35.     MsgBox "Please be patient while application is uploading the contracts.", vbInformation
  36.  
  37.  
  38.     ' import file
  39. '    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SO_Data", "L:\UH_Warehouse_Reporting\SciQuestData\SO_Data.xls", True
  40. '    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SO_CFHeader", "L:\UH_Warehouse_Reporting\SciQuestData\SO_CFHeader.xls", True
  41. '    'DoCmd.TransferText acImportDelim, "Credit_Memo", "L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.csv", True
  42. '    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Credit_Memo", "L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.xls", True
  43. '
  44. '    FOR TESTING PURPOSES
  45.  
  46.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SO_Data", "C:\Documents and Settings\DowlinMM\My Documents\Development\UH Mgt Inventory Rptg\UH Mgt Inventory Rptg\Sciquest Data\SO_Data.xls", True
  47.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SO_CFHeader", "C:\Documents and Settings\DowlinMM\My Documents\Development\UH Mgt Inventory Rptg\UH Mgt Inventory Rptg\Sciquest Data\SO_CFHeader.xls", True
  48.     'DoCmd.TransferText acImportDelim, "Credit_Memo", "L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.csv", True
  49.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Credit_Memo", "C:\Documents and Settings\DowlinMM\My Documents\Development\UH Mgt Inventory Rptg\UH Mgt Inventory Rptg\Sciquest Data\Credit_Memo.xls", True
  50.  
  51.  
  52.     MsgBox "Task Complete!", vbInformation
  53.  
  54. ImportRecords_Exit:
  55.   Exit Sub
  56.  
  57.  
  58. Err_Hndlr:
  59.     MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "ImportRecords()"
  60. End Sub
  61.  
Apr 5 '11 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
The importspec is the details of how the import is meant to run. For instance, in the DoCmd.Transfertext, you have specified that there is a delimiter, but you have not specified what the delimiter is! Such a thing among others are specified in an import spec. If you manually import the file the first time, you can specify such settings, and then save the import spec.
Apr 5 '11 #8

100+
P: 133
It took a bit a digging, but the following link is how to create an export /import specification in MS Access. My error is that I was looking to code it:

http://www.blueclaw-db.com/export-specifications.htm
Apr 13 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.