Troubleshooting Bulk Imports

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. VPASP'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
For Each Cell In Line.Cells
StrTemp = StrTemp & Chr(34) & Trim(CStr _
(Cell.Text)) & Chr(34) & Separateur
Next
StrTemp = StrTemp & vbCrLf
Next
Print #1, Mid(StrTemp, 1, Len(StrTemp) - 1)
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 VPASP.
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 VPASP csv file called Export.csv.
10. This export.csv file will be correctly delimited and you should have no problems importing to VPASP.


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 VPASP.

VP-ASP 6.50
If you are using VP-ASP 6.50 and 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).

New Line Character

Another issue when importing the data is when your import file contains new line character in it. Unfortunately the file cannot have this character in it.

You would need to change the new line character into <br /> HTML tag. As basically, the import file is treated as a plain text file that could only have HTML tags in it.

To do so, please use the following Excel macro:

Option Explicit
Sub RemoveRtns()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
cel = Replace(cel, Chr(10), "<br />")
Next
End Sub


Times Viewed:
4719
Added By:
michael
Date Created:
8/15/2006
Last Updated:
11/23/2011