Importing a CSV file


V

Vince

I want to import a file into physical inventory. Here is what I do:
1) Run a Quantity report(I don't think the report really matters)
2) I put it in alphabetical order, then I export it using RMS Manager into a
..CSV file
3) I go into Inventory > Physical Inventory, and I click on New and Manuel
Entry
4) I import the file...it works just fine!!!
BUT
If you in any way alter the .CSV file, and save it again, in the same format
Steps 1 - 4 won't work...WHY WHAT THE HECK AM I DOING WRONG???
 
Ad

Advertisements

R

Rob @ RMS Ltd

The problem might be the Item Lookup Codes are being modified by Excel while
in a CSV format.
For long numbers Excel will display the numbers as Scientific then once you
save the file it saves it with 000000's rather than the real number.
For example:
Item Lookup Code: 0745687456555
Excel in CSV format displays as: 7.46E+11
After Saving ILC converted to: 745687000000
Also Excel will remove any 000's from the beginning of an Item lookup Code.
Notice Example above.
Once you try to import this back into RMS those Item Lookup Code values will
no longer match what you have in your database.

You will need to Import the Data into an Excel worksheet and make sure you
set the Column data type to Text rather than General.
Once you have made the desired changes save the file as a CSV file but do
not open and save the CSV file.

Now you should be able to import the CSV back into your Physical Inventory
sheet.

Robert Armstrong
RMS Systems Inc.
 
V

Vince

Rob,
I understand the potential problem you are talking about, but I don't
understand how to resolve this...
1) How do I Import the file into an Excel worksheet?
2) Setting the column to Text, no problem...
3) Not sure I understand the last part either...Once you have made the
desired changes save the file as a CSV file but do not open and save the CSV
file???

When I try to save it back into a CSV file (after opening, since I can't
seem to import), I am being told that certain data will be lost, and one of
them is the Text option that I select!
--
Thank You
Vince :)


Rob @ RMS Ltd said:
The problem might be the Item Lookup Codes are being modified by Excel while
in a CSV format.
For long numbers Excel will display the numbers as Scientific then once you
save the file it saves it with 000000's rather than the real number.
For example:
Item Lookup Code: 0745687456555
Excel in CSV format displays as: 7.46E+11
After Saving ILC converted to: 745687000000
Also Excel will remove any 000's from the beginning of an Item lookup Code.
Notice Example above.
Once you try to import this back into RMS those Item Lookup Code values will
no longer match what you have in your database.

You will need to Import the Data into an Excel worksheet and make sure you
set the Column data type to Text rather than General.
Once you have made the desired changes save the file as a CSV file but do
not open and save the CSV file.

Now you should be able to import the CSV back into your Physical Inventory
sheet.

Robert Armstrong
RMS Systems Inc.
 
R

Rob @ RMS Systems Inc

1. Import using Excel. There should be an Import option under the Data
tab/menu to import from Text file.
2. You set the Text file during the import Wizard.
3. What I mean regarding "do not open and save the CSV file" is because a
CSV file does not save column formatting and if you edit the file after
saving you will end up with the same results that I explained in my original
post.
"I am being told that certain data will be lost, and one of them is the
Text option that I select!" Same issue as above.
Steps again would be:
1. Export from RMS.
2. Import CSV file into an Excel Worksheet using the Import Wizard in Excel.
Making sure you set the ILC column to Text.
3. Save file as an XLS file not CSV.
4. Once all changes are made in Excel THEN save as CSV.
5. Use the new CSV file to import back into RMS

Robert Armstrong
RMS Systems Inc.


Vince said:
Rob,
I understand the potential problem you are talking about, but I don't
understand how to resolve this...
1) How do I Import the file into an Excel worksheet?
2) Setting the column to Text, no problem...
3) Not sure I understand the last part either...Once you have made the
desired changes save the file as a CSV file but do not open and save the
CSV
file???

When I try to save it back into a CSV file (after opening, since I can't
seem to import), I am being told that certain data will be lost, and one
of
them is the Text option that I select!
 
I

IT

Vince wrote:.....
.......
Steps 1 - 4 won't work...WHY WHAT THE HECK AM I DOING WRONG???
We use the Import Utility for adding basic SKU and description
information and we generate the .csv file(s) in Excel. I've noticed that
Excel will sometimes put quotation marks around the entries in certain
fields - you might want to open your "import ready" file in Word or a
text editor and see if this is the case. If it is, a simple "search and
replace" solves the problem.

George
 
Ad

Advertisements

V

Vince

Awesome...worked like a charm...took a few tries importing, but I got
it...thank you very much!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top