csv FORMAT FILE PROBLEMS

**pick44** · 08-08-2020, 09:30 AM

I have a program that uploads csv files and provides Lat and Long coordinates. In all instances, the files upload and Lat and Long are provided for each address.

All except one file. I had to convert it from pdf to excel and then save as csv. I have attached a sample. I cannot see any issues with the file, but there must be. I have tried to copy and paste to new spreadsheet , but that did not work either.

I am not sure if you can help on this one, but you have been 100% on my other issues in the past, and I count on you for your help. You have always found solutions.

This is a little different, but see if you can find the problem. My data has columns that are formatted as numbers, ( incident, code) and general, (description, address, city) and as date and time. They all look okay. But somewhere there is a problem.

Hope you can give me some ideas. Thanks in advance.

Windows 10, Excel 2016

**AliGW** · 08-08-2020, 10:01 AM

Can you add a column to show what the Lat/Long codes should look like for the first few lines of data?

Even better: attach the original .pdf.

**pick44** · 08-08-2020, 10:15 AM

The original pdf has 240,000 rows of data with 11 columns. If you want the file, I can send. I have attached the csv file with Lat and Long numbers. Thanks

**AliGW** · 08-08-2020, 10:23 AM

Yes, but the .csv file does not show what they should look like. Please save the first page of your .pdf and just attach that, or at the very least, attach a screenshot of it.

How EXACTLY do you convert the .pdf file to an .xlsx file?

**pick44** · 08-08-2020, 11:13 AM

I convert pdf with PDF2XL. Screen shot of first page of pdf attached. Thanks

**AliGW** · 08-08-2020, 11:17 AM

Where are the coordinates??? This is what I s in your .csv:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	INCIDENT	CODE	DESCRIPTION	DATE	TIME	STREET	CITY
2
3	180073033	18	NARCOTICS	05/08/2018	15:15	1 GOODSON DR	HOUSTON
4	180907557	18	POSS MARIJ <2OZ	9/25/18	15:13	100 AIRTEX DR	HOUSTON
5	160175689	18	NARCOTICS	11/02/2016	14:56	100 AIRTEX DR	HOUSTON
6	150116025	18	NARCOTICS	7/28/15	16:35	100 AIRTEX DR	HOUSTON
7	150070093	18	NARCOTICS	05/08/2015	20:24	100 AIRTEX DR	HOUSTON
8	150068243	18	NARCOTICS	05/05/2015	20:07	100 AIRTEX DR	HOUSTON
9	150041374	18	NARCOTICS	3/19/15	20:11	100 AIRTEX DR	HOUSTON
10	181208106	18	POSS DANGEROUS DRUG	12/25/18	22:13	100 AIRWAY DR	HOUSTON
11	160066458	18	NARCOTICS	4/25/16	18:44	100 ALDINE BENDER RD	HOUSTON
12	160066466	18	NARCOTICS	4/25/16	18:44	100 ALDINE BENDER RD	HOUSTON
13	160170021	18	NARCOTICS	10/24/16	02:50	100 ALDINE MAIL RD	HOUSTON
14	170018193	18	NARCOTICS	02/01/2017	23:51	100 AUSTIN ST	HOUSTON
15	150038167	18	NARCOTICS	3/13/15	18:56	100 BAKER RD	HOUSTON
16	150072484	18	NARCOTICS	5/13/15	00:50	100 BAMMEL WESTFIELD RD	HOUSTON
17	160095085	18	NARCOTICS	6/13/16	13:30	100 BENMAR DR	HOUSTON
18	181108109	18	POSS CS PG 1 <1G	11/26/18	01:54	100 DALE ST	HOUSTON

Sheet: TEST TEST HCSO-1

What exactly is it that needs fixing?

**pick44** · 08-08-2020, 11:25 AM

Coordinates 2 TEST TEST file attached. This file will not "match" up to PDF I sent. The problem is in the "upload" to my program that takes the address, city, and
produces the Lat and Long. For every other file I have upload that was not a pdf conversion to excel, but was an excel file to start with, has uploaded and Lat and Long
given. This file, which was pdf and converted to excel and then to csv for uploading, will not convert.

thanks,

**AliGW** · 08-08-2020, 11:29 AM

Sorry - I know you will think I am being dense, but what exactly do you mean by "will not convert"? You have attached two files and a screenshot, but still not told me where I should be looking for the problem.

**pick44** · 08-08-2020, 11:34 AM

Not sure myself "what the problem is".
Will not convert is that no Lat and Long are assigned once I upload the file in the GPS program that I use. I think it must be in a flaw in the csv that my program does not like

. That is why I am thinking there is something I cannot see in the csv file that may contain corrupted areas?

Thanks

**AliGW** · 08-08-2020, 11:37 AM

OK - so how about you attach a .csv file that does work, and maybe we can compare and see if there is any difference?

Have you tried appending the 'rogue' data to a .csv that you know works and tried uploading that?

**pick44** · 08-08-2020, 11:45 AM

See attached

**AliGW** · 08-08-2020, 11:48 AM

Have you tried appending the 'rogue' data to a .csv that you know works and tried uploading that?

**AliGW** · 08-08-2020, 11:53 AM

If you open the file I attached above in Notepad, you will see that the 'good' data has trailing spaces after the name of the crime: do each of the fields need to be a specific number of characters?

This suggests that the .csv format needs to have fixed width fields, possibly.

**Haluk** · 08-08-2020, 11:58 AM

The main difference i noticed is; the "DESCRIPTION" field has a constant 30 characters width in the CSV file which seems generating the coordinates as desired, where this is not true for the file which is told to be not working.

So, i can suggest to add space characters at the end of the "DESCRIPTION" field as required to fill up the width to 30 characters length.

**AliGW** · 08-08-2020, 11:59 AM

That's what I was referring to in post #13.

**Haluk** · 08-08-2020, 12:03 PM

Ali, didn't see your post before posting my msg.

**AliGW** · 08-08-2020, 12:05 PM

It's no problem - I was just agreeing with your assessment.

**pick44** · 08-08-2020, 12:32 PM

For my other files, the column width can be 8.43 or larger and still it is okay to get the Lat and Long. The program is suppose to key off the Address column. And in some cases when I upload, the width is 8.43 and it works. I will look at the trailing spaces issue raised by Ali

**AliGW** · 08-08-2020, 12:33 PM

We are not talking about the WIDTH of the column - that's completely irrelevant. We are talking about the number of characters in the cells.

Excel 2016 (Windows) 32 bit

	A	B	D
1	DESCRIPTION	City	No. of
2			characters
3	THEFT BY SHOPLIFTING	Austin	30
4	THEFT BY SHOPLIFTING	Austin	30
5	BURGLARY OF VEHICLE	Austin	30
6	BURGLARY OF VEHICLE	Austin	30
7	THEFT	Austin	30
8	THEFT BY SHOPLIFTING	Austin	30
9	THEFT BY SHOPLIFTING	Austin	30
10	NARCOTICS	HOUSTON	9
11	POSS MARIJ <2OZ	HOUSTON	15
12	NARCOTICS	HOUSTON	9
13	NARCOTICS	HOUSTON	9
14	NARCOTICS	HOUSTON	9
15	NARCOTICS	HOUSTON	9
16	NARCOTICS	HOUSTON	9
17	POSS DANGEROUS DRUG	HOUSTON	19
18	NARCOTICS	HOUSTON	9
19	NARCOTICS	HOUSTON	9
20	NARCOTICS	HOUSTON	9
21	NARCOTICS	HOUSTON	9
22	NARCOTICS	HOUSTON	9
23	NARCOTICS	HOUSTON	9
24	NARCOTICS	HOUSTON	9
25	POSS CS PG 1 <1G	HOUSTON	16

Sheet: APPENDED 3 test test will conve

**pick44** · 08-08-2020, 12:52 PM

Okay. thank you. However since the Street column in my upload ranges from a high of 62 to 3 and the good upload sample ranges from 61 to 4. they are almost the same.

Since the Lat and Long program grabs the Street column, I must believe that there is something in the csv file format either, in the Street Column or ??? The program does upload all the data succesfully but when it begins to provide Lat and Long, it does not. It says " 0 Lat and Long have been applied to X#".

**AliGW** · 08-08-2020, 01:02 PM

OK - look, please don't take this the wrong way, but if it were me, I'd stop making assumptions and actually try adding the spaces needed to the 'rogue' file and see if it DOES make a difference before dismissing it. If it doesn't, fine, but if you don't try it, we'll never know. You asked for help trying to establish what the issue is: if you are going to ignore us, then there's not much point in our looking and trying to help.

If the receiving software is looking for fixed width in the description column, then everything that follows it will be starting too early for it to interpret. Does this make sense? So although it's taking data from other columns, it has to be able to read the others, too, in the format it's expecting.

**pick44** · 08-08-2020, 01:40 PM

Yes, makes sense. Was not ingoring you. Thought I was following your requests. No problems. Did not take the wrong way. I am happy to learn. My job is a stressful one, not complainging, and perhaps I get to focused and miss the obvious request. So, I will try. Thank you

**pick44** · 08-08-2020, 01:47 PM

No difference after adding the spaces.

**AliGW** · 08-08-2020, 05:04 PM

That’s a shame. I am not sure what else to suggest - sorry.

**pick44** · 08-08-2020, 05:10 PM

Well, I will eventually fiugre this out. Always appreciate all of you helping me from time to time. Have a blessed weekend. Rich ><>

csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Re: csv FORMAT FILE PROBLEMS

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Format Problems

[SOLVED] Excel 2007 File Problems (Opening New Format)

Bookmarks

Bookmarks

Posting Permissions