topic Re: CSV file changes numeric data to character in Discussions

CSV file changes numeric data to character

Zara — Wed, 23 Aug 2023 08:24:33 GMT

When I open the CSV file in JMP all the numeric turns into character and by changing column info the data either disappears or randomly changed into various incorrect decimal formats, should I try any other tools? methods to open the file?

Re: CSV file changes numeric data to character

jthi — Wed, 23 Aug 2023 08:47:27 GMT

In what type of format is your data in? Could it even be converted to a number? Could you share the .csv file?

For example:

Re: CSV file changes numeric data to character

Zara — Wed, 23 Aug 2023 10:19:00 GMT

unfortunately, I cant share the csv file, the data is 3 column the tag name, data and data points of an online measurement time series. The tag names and date info transfers properly, but data transfers properly in character format and when I change it to continuous, all I explained happens... here is a couiple od data points:

-0,17548
0,03195
0,13924
0,272756
3,183891
3,64643
1,395725
0,947491
0,678074
0,55171
0,10586
0,055792
0,282293
34,41482
90,42973
100,8011
102,4915

I also open data with using preview, It solved the problem without pushing any other change but I received this message, and since the data set has more tha 1 mil, rows, I am still not sure if the data transfers properly.

Random check however looks ok with data as continuous and decimals

Re: CSV file changes numeric data to character

Craige_Hales — Wed, 23 Aug 2023 10:55:48 GMT

The alert dialog means JMP found values in a numeric column that could not be converted to numbers. It replaced the value with a missing value. It continued on after that, so the rest of your data should be okay. The suggestion about converting the column to character is just so you could investigate what the odd value was.

Re: CSV file changes numeric data to character

Craige_Hales — Wed, 23 Aug 2023 10:57:26 GMT

Also, a question about your sample of data. Are those commas delimiters between values or are they decimals?

Re: CSV file changes numeric data to character

Zara — Wed, 23 Aug 2023 11:05:07 GMT

Thanks, cammas are decimals in source data, the visual investigation is not a good solution to find odd values in such a big number of raws (15478), I prefer having 186 missing values... however this is a subset of much larger data, so 186 leave out from a sample can be much more in the big data without knowing the reason is not a safe situation, thats why I am looking for a proper import of csv files into JMP, I think I do not know all tips and tricks here in transfering data from csv to JMP

Re: CSV file changes numeric data to character

Zara — Wed, 23 Aug 2023 11:07:36 GMT

a subset of 1542834 raws

Re: CSV file changes numeric data to character

Craige_Hales — Wed, 23 Aug 2023 11:10:24 GMT

Are the values in your file delimited by tab characters? Or is the import being confused by commas being used two different ways?

Re: CSV file changes numeric data to character

Craige_Hales — Wed, 23 Aug 2023 11:18:21 GMT

Could you edit and copy the source script from the imported table and show us what it looks like. That will give us some information about what might be going wrong. If you could give us one line of the data as seen by notepad, that would also help.

Re: CSV file changes numeric data to character

Zara — Wed, 23 Aug 2023 11:21:01 GMT

In source csv file the data delimited by camma, and decimals are dots, when it transfers to jmp I see no delimitation problem, and the dot(.) transferred to comma for decimals in character format, or with the other solution and the message I find data properly transferred both with delimitation and for the decimals, however I have the same sense that missing values are related to comma/dot decimals maybe??

Re: CSV file changes numeric data to character

Craige_Hales — Wed, 23 Aug 2023 11:43:44 GMT

You should try File->ImportMultipleFiles, which will also work for a single file. It uses a different algorithm and does not try to guess about the file's format. It can be faster too. If it doesn't work, maybe there will be a message that helps figure this out.

Re: CSV file changes numeric data to character

Zara — Thu, 24 Aug 2023 09:07:56 GMT

Thanks again, I tried all I could with the import Multiple- as data again it will change to character in all choices and when I want to change the info and make them numeric-continuous, most of the data disappears

No messages and software will ask to be closed at some cases, looks like the data is too big to handle ??

as text, is there any way to split a column of text to more columns??

Re: CSV file changes numeric data to character

Craige_Hales — Thu, 24 Aug 2023 13:26:46 GMT

Your file is not too large.

Without seeing the data it is hard to say what is happening. My guess is there are some characters in the file that look like commas or periods or quotation marks but are actually other Unicode characters. If you can isolate a line or two of the file that cause the problem in an editor (notepad, JMP, etc) it might become obvious. You might also try opening with excel and see if that produces a diagnostic message that helps.

You can also work with JMP tech support in a more private conversation...Support tab at top of page.

Re: CSV file changes numeric data to character

Craige_Hales — Thu, 24 Aug 2023 13:57:15 GMT

Two videos since you asked about importing the file as text and splitting...

first, the hard way, importing lines then splitting them

(view in My Videos)

next, the easy way (which apparently isn't working for you...)

(view in My Videos)

Re: CSV file changes numeric data to character

Zara — Mon, 28 Aug 2023 12:05:58 GMT

Thanks a lot Craige