X-Cart forums

[Stephen Hatton]

Hi All

As promised before last Christmas, here is my method for porting Xcart csv files to M$ Excel for manipulation and then back to Xcart.

I hope it helps you. It has taken a lot of Blood, Sweat, Tears and COFFEE to get it right. So from one non-programmer to others with limited experience, I wish you all the best with your cart.


XCART to EXCEL Ultraedit Macros
************************************************** ***
by Stephen Hatton
22 January 2005

Description:
This Macro is used to convert the csv file generated by XCART (with semicolons as a delimiter) to a text file that can be imported to M$ Excel (version 97).
M$ Excel does not accept files with HTML parts cleanly as text only. It always tries to interpret them and display them as HTML in the spreadsheets. Also Excel will accept "12323" from the Xcart dump as numbers and when you export the file as a csv (to restore into XCART), it does not put quotes around them. Additionally Excel will add and/or remove quotes across the file in all cells depending on the data contained, including double quoting some cells.
All the things above makes it impossible to Export XCART database as a csv, import it into M$ Excel, Export it as a csv again and import it back to XCART. You don't even get a chance to edit the file without the process falling over.

Now I haven't tried M$ Excel version 2000 or XP, however from internet searches on HTML breakup, all versions are affected (from the frustration of many users). So if you use any HTML to jazz up the fields and web layout, you will encounter this problem (otherwise you are stuck with the default XCART syle and layout).

OpenOffice also tries to interpret HTML codes, so it is not a solution either (unless somebody knows what button to press or option to change to disable HTML recognition).

These macros have been written for Ultraedit, however you can use the procedures for any editor (including word), just be careful to save as text only and watch out for any additional (including hidden) characters being added. A programmer's editor is recommended.

Procedure for XCART - export/import - Excel - export/import - XCART
************************************************** ***
1/ Export your XCART product database as product.csv with semicolons as field separators (default XCART settings).
2/ Save file to your local hard disk.
3/ Open the file with your editor (UltraEdit in my case - DOS mode) and run the XcartExcelTSV macro. It will convert the file and save converted file as c:\data\products_TSV.txt (make sure you have a directory there).
4/ Open the products_TSV.txt file with M$ Excel and edit your database.
5/ Check that all the column data lines up (ie. with similar data) and that nothing has gone wrong with the importing.
6/ When you edit the database, make sure that every field starts with [ and ends with ]. If you have to embed quotes, then use ~quote~, otherwise they will be stripped by the next macro. Preferably put <~ for HTML tags or you will have phun with Excel!
5/ You can copy and move lines about, but be aware of the database structure!!!!
6/ After editing, save the file as a tab delimited text file by Excel to c:\data\products_TSV_out.txt.
7/ Then load the products_TSV_out.txt file with your editor (UltraEdit in my case - DOS mode) and run the XcartExcelTSV macro. It will convert the file and save converted file as c:\data\products_TSV_out_txt.csv in Unix format.
8/ Login to your adminstrator section of your shopping cart.
9/ BACKUP YOUR XCART SQL DATABASE BEFORE PROCEEDING!!! Alternatively you can also backup your database with cPanel if you have access to this program on your host.
10/ I would highly recommend testing the next stages on your test site before applying it to your live cart.
11/ Close your shop from customer accessing the cart before importing products (look in the "general settings" area).
12/ Import products, dropping existing products (loading the products_TSV_out_txt.csv file).
13/ Fingers crossed!!@!??? Hopefully Murphy has his day off and everything works.
14/ Logout of the administrator area and check the customer shop data using in the closed shop key (V4 cart only) for entry.
15/ If everything looks OK, then enable the shopping cart through the administrator area, if not, restore the database (you DID make a BACKUP???!).
16/ Have a coffee and relax....... If there was a problem, look at the files and see if there is an obvious problem. If there was no problem - good - but please check the shopping cart for any glitches not seen earlier.
************************************************** ***
Problem solving: If there is a problem with the macros, the best test is to convert one file from Xcart (.csv > TSV.txt > Excel (open but do nothing except save) > TSV_out.txt > TSV_out_txt.csv) and bit by bit compare the original .csv file with the TSV_out_txt.csv file. I use the DLSupCBT program.

************************************************** *******************
************************************************** *******************
************************************************** *******************
The following macro (XcartExcelTSV) will convert all quotes to recoverable quotes [], semicolon field delimiters to tab delimiter, all fields to text fields only (numbers are a problem for export/import-Excel-export/import), and all HTML tags to safe tags before importing into M$ Excel.

Macro Name: XcartExcelTSV
************************************************** *
InsertMode
ColumnModeOff
HexOff
UnixReOff
Top
Find "~"
Replace All "!tilde!"
Find "]"
Replace All "~sqclose~"
Find "["
Replace All "~sqopen~"
Find "^t"
Replace All "~tab~"
Clipboard 1
ClearClipboard
Find "^p"
SelectToTop
Cut
Top
"["
Key DEL
Find """
Replace All "~quote~"
Find "~quote~;~quote~"
Replace All "]^t["
Find "~quote~^p~quote~"
Replace All "]^p["
Bottom
Find Up "~quote~^p"
Replace "]^p"
Find ";"
Replace All "~semicolon~"
Find "~quote~~quote~"
Replace All "~dquote~"
Find "<"
Replace All "<~"
Top
Paste
Find ";"
Replace All "^t"
TrimTrailingSpaces
SaveAs "c:\data\products_TSV.txt"
************************************************** *******************

Commented macro version for others to convert to other editors:
************************************************** *******************
InsertMode Standard Default UE macro line
ColumnModeOff Standard Default UE macro line
HexOff Standard Default UE macro line
UnixReOff Standard Default UE macro line
Top
The following instructions convert any ~, ], [, and tabs in the file so that we can use them as delimiters in our TSV file plus we can restore them after editing in Excel and importing back into Xcart

Find "~" }
Replace All "!tilde!" } Find all ~ (tilde) in csv file and replace with: !tilde!
Find "]" >
Replace All "~sqclose~" > Find all ] in csv file and replace with: ~sqclose~
Find "[" }
Replace All "~sqopen~" } Find all [ in csv file and replace with: ~sqopen~
Find "^t" >
Replace All "~tab~" > Find all tab characters in csv file and replace with: ~tab~

Now we convert the ", ;, < delimiters to things that are not converted or destroyed by Excel

Clipboard 1 } Switch to clipboard 1 (away from the windows clipboard)
ClearClipboard } Clear the clipboard
Find "^p" >
SelectToTop >
Cut >
Top > Select the first line and cut to clipboard 1 (the first line has the column/field names which is formatted differently to the product data)
"[" }
Key DEL } Convert the first " (quote) to an open square bracket
Find """ >
Replace All "~quote~" > Find all " (quote) and replace with ~quote~
Find "~quote~;~quote~" }
Replace All "]^t[" } Find all ~quote~;~quote~ and replace with ];[ (ie. replacing the ";" csv field separators)
Find "~quote~^p~quote~" >
Replace All "]^p[" > Find all ~quote~^p~quote~ and replace with ]^p[ Note: ^p = carriage return character
Bottom }
Find Up "~quote~^p" }
Replace "]^p" } Find the last ~quote~ in the data and replace with ]
Find ";" >
Replace All "~semicolon~" > Find all ; (semicolons) in the text (eg. HTML - ) and replace with ~semicolon~
Find "~quote~~quote~" }
Replace All "~dquote~" } Find all ~quote~~quote~ and replace with ~dquote~
Find "<" >
Replace All "<~" > Find all < (eg. HTML <tr> ) and replace with <~ Note: the tilde stops Excel interpreting HTML
Top }
Paste } Go to top of document and paste in first line again from clipboard
Find ";" >
Replace All "^t" > Replace any ; (semicolons) in the first line with tab character
TrimTrailingSpaces }
SaveAs "c:\data\products_TSV.txt" } Remove any spaces at the end of each line and save the file as _TSV.txt (note: TSV stands for Tab Separated Variable file standard format). The .txt extention forces Excel to ask questions before importing.
************************************************** *******************
************************************************** *******************
************************************************** *******************




************************************************** *******************
************************************************** *******************
************************************************** *******************
The following macro (ExcelXcartCSV) will strip all rubbish inserted by Excel, convert all recoverable quotes [] to quotes ", tab delimiter to semicolon field delimiters, all data fields to text fields inside quotes, and HTML tags back to the original format before importing into XCART.

Macro Name: ExcelXcartCSV
************************************************** *
InsertMode
ColumnModeOff
HexOff
UnixReOff
Find """
Replace All ""
Find "["
Replace All """
Find "]"
Replace All """
Find "^t"
Replace All ";"
Find "~quote~"
Replace All """
Find "~dquote~"
Replace All """"
Find "<~"
Replace All "<"
Find "~semicolon~"
Replace All ";"
Find "~tab~"
Replace All "^t"
Find "~sqopen~"
Replace All "["
Find "~sqclose~"
Replace All "]"
Find "!tilde!"
Replace All "~"
DosToUnix
TrimTrailingSpaces
SaveAs "c:\data\products_TSV_out_txt.csv"
************************************************** *******************

Commented macro version for others to convert to other editors:
************************************************** *******************
InsertMode Standard Default UE macro line
ColumnModeOff Standard Default UE macro line
HexOff Standard Default UE macro line
UnixReOff Standard Default UE macro line

The following instructions convert any ~, ], [, and tabs in the file so that we can use them as delimiters in our TSV file plus we can restore them after editing in Excel and importing back into Xcart

Find """ }
Replace All "" } Find all quotes inserted by Excel and remove them (replace them with nothing)
Find "[" >
Replace All """ > Find all [ and replace with " (quotes)
Find "]" }
Replace All """ } Find all ] and replace with " (quotes)
Find "^t" >
Replace All ";" > Find all tab characters and replace with ; (semicolons)
Find "~quote~" }
Replace All """ } Find all ~quote~ and replace with " (quotes)
Find "~dquote~" >
Replace All """" > Find all ~dquote~ and replace with "" (two quotes)
Find "<~" }
Replace All "<" } Find all <~ and replace with <
Find "~semicolon~" >
Replace All ";" > Find all ~semicolon~ and replace with ; (semicolon)
Find "~tab~" }
Replace All "^t" } Find all ~tab~ and replace with tab characters
Find "~sqopen~" >
Replace All "[" > Find all ~sqopen~ and replace with [
Find "~sqclose~" }
Replace All "]" } Find all ~sqclose~ and replace with ]
Find "!tilde!" >
Replace All "~" > Find all !tilde! and replace with ~
DosToUnix } Change format of file from dos to unix (to be more compatible with xcart import)
TrimTrailingSpaces > Strip spaces at the end of the lines
SaveAs "c:\data\products_TSV_out_txt.csv" } Save the file as products_TSV_out_txt.csv

************************************************** *******************
************************************************** *******************
************************************************** *******************
Note: The file names have a progression method:
products.csv (XCART export file)
products_TSV.txt (converted file in TSV format for Excel import)
products_TSV_out.txt (file exported by Excel in TSV format for converting)
products_TSV_out_txt.csv (file converted back to XCART format with field quotations and semicolon separators)



So as I said earlier in the forum, I have a procedure (it is not easy for beginners) but it works. If somebody else has a simpler method or some more info, please post it for others to use.

Regards
Ing. Stephen Hatton