Problem Importing Delimited Text into MDM Using Import Manager

[Former Member]

(this is my first post, so here goes...)

When attempting to import a set of data into MDM using import manager, I run into the following error:

"Error Connecting to Source

Logon Error:
(the file's location on a network drive): Line 2 (Pos: 1)."

This is what line 2 looks like in my text file:

112396087218;1" Square Drive Handles

I've figured out that the (") symbol is what is tripping import manager up and preventing it from loading the text file. If I take out that character from the above line, it loads the text file correctly into import manager. My guess is that this means that import manager doesn't play well with special characters. Unfortunately, there are quite a few terms with various special characters within my text file.

I have the text file saved as Unicode rather than ANSI, but that doesn't seem to help.

Any ideas? Your help would definitely be appreciated.

Edited by: mathewc on Dec 29, 2010 6:58 PM

[Former Member · Accepted solution]

Hi,

Rightly suggested by Priti, you can change console repository Property Use CSV format to import delimited text files = Yes. I hope after changing it to yes, your issue would be resolved.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mathew,

I have come across same kind of problem.what did was:

1. I have changed to ANSI format

2. We have to find that line where that error is thorughing .If you chek in that line its probably open braket.

For example : "uploaded succesffully" - in this case it will be successful.

upload successfully" - in this case it will be failed to import.

Please check in this way also. Thank you.

[former_member208981]

Hi Mathew,

(first reply to your first post.. and here it comes )

Please refer the SAP Note Number: 1438116 (Delimited text files fail to open with the Import Manager)

Symptom

Delimited text files fail to open with the Import Manager with error message: "Logon Error: \<source path>\<File source name>: Number of field values does not match the field columns. Line XX [Pos XX]."

Solution:

A new repository property was added which allows you to import text files that have quotation (") marks in them by changing the default value from True to False.

Right click on the repository in the MDM Console in the Repositories pane and select Properties.

Modify the property "Use CSV format to import delimited files" accordingly to your need.

NOTE:

It is not possible to import files where the data contains both ' and ".

In such cases where your souce file contains both ' and " we would recommend that you convert the files into Excel sheets or XML format. This will enable you to import the files as desired without regard to either the ' or " being in the source file.

Thanks,

Priti

[Former Member]

Thanks for the suggestions, but my data set does contain entries with both (') and (") in the text fields. Looks like I should be using Excel files instead. The terms also don't have " appearing in pairs (7" Pipe) at times.

I'm curious as to how Import Manager can accept those characters in excel spreadsheets but not text files. Any ideas on why that is? I can't export using .csv files in Excel however because my terms will occasionally have commas within them (Air, Water, and Oil Hose).

I'm currently using a tab delimiter when I put them in an excel file and then working out how to split the column into two (one column with the term, one column with the part number). Once I put that in import manager and try it out, I'll post whether or not that works.

Wish me luck!

[Former Member]

It looks like the solution to my problem is to use an excel file to import the data rather than a delmiited text file due to the nature of my data set (special characters, etc.)

In case other people want to reference how the workaround went, this is what I did:

1.) saved my SQL data as a .csv file and set it to encode in Unicode and used tab delimiters instead of commas (because my data set has commas in the terms).

2.) used a program called CSV splitter to take my large file (500+ mb) and turn it into about 20 files that are about 500,000 rows a piece. This is because import manager throws an unknown error when I try to get it accept anything near 1,000,000 rows per file, and excel can't open any files with more than approx 1,000,000 rows.

3.) opened the .csv files in excel and split the text into two columns (one for terms, the other for product numbers) and saved it as .xlsx files.

4.) ran each file through import manager (having to set the mappings, etc. for each one as I went along).

5.) success!

Thanks for the suggestions, and it's nice to have finally figured out a workable method for this large data import. Now I just have to setup the time to run the jobs over the weekend. The test batch did well last night, so I'm optimistic that the rest of the files should go through just fine.