Troubleshooting Bulk Imports
Problem: You may receive an error such as one of the following when attempting to import a csv file after exporting from Excel:
Microsoft VBScript runtime error '800a0009'
Subscript out of range: 'index'
/shopa_import.asp, line 140
Provider error '80020005'
Type mismatch.
/shopa_import.asp, line 513
These errors are generally caused by either an incorrect import of your csv file into Excel, or by an invalid export from Excel.
Excel by default does not delimit all fields with double quotes. ASP Shopping Cart's bulk import tool requires all fields to be correctly delimited with double quotes. One way around Excel's export deficiency is to create a macro to correctly delimit your export file.
To rectify this please try the following:
1. On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type the following code into the code window of the module.
Sub SaveAsCSV()
Dim Range As Object, Line As Object, Cell As Object
Dim StrTemp As String
Dim Separateur As String
Separateur = ","
Set Range = ActiveSheet.UsedRange
Open "Export.csv" For Output As #1
For Each Line In Range.Rows
StrTemp = ""
For Each Cell In Line.Cells
StrTemp = StrTemp & Chr(34) & Trim(CStr _
(Cell.Text)) & Chr(34) & Separateur
Next
Print #1, Mid(StrTemp, 1, Len(StrTemp) - 1) '= " "
Next
Close
End Sub
4. If you want to run the macro from the module window, press F5.
5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu.
6.Open your csv file which contains all of your product information from ASP Shopping Cart.
7.Make your required changes.
8.When ready to export, instead of using excel's Save As function, go to Tools > Macro > Macros... and highlight the SaveAsCSV Macro and click Run
9.This will create a new file in the same directory as your ASP Shopping Cart csv file called Export.csv.
10. This export.csv file will be correctly delimited and you should have no problems importing to ASP Shopping Cart.
Similarly the importing of a csv into Excel can cause issues if you have not correctly identified the delimiter.
When you import data into excel from a txt file, there is a field that says "Text qualifier" and its default value="
When you change this to "none", then the quotes appeared in the imported data. You can then make your changes, then save it as a csv and re-import it into ASP Shopping Cart.
ASP Shopping Cart Hosting Server
If you are using SQL Server, it may be that the date delimiter is not set correctly for your server. In your shop configuration, look for the variable xdatedelimiter.
The most common values for this are "/" or "-" (without quotes).