+ Reply to Thread
Results 1 to 25 of 25

csv FORMAT FILE PROBLEMS

  1. #1
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    csv FORMAT FILE PROBLEMS

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

    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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    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?

  5. #5
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

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

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    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?

  7. #7
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

    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,
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    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.

  9. #9
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

    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

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    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?

  11. #11
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

    See attached
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    Have you tried appending the 'rogue' data to a .csv that you know works and tried uploading that?
    Last edited by AliGW; 08-08-2020 at 11:50 AM.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    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.
    Last edited by AliGW; 08-08-2020 at 11:58 AM.

  14. #14
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    706

    Re: csv FORMAT FILE PROBLEMS

    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.
    Last edited by Haluk; 08-08-2020 at 12:02 PM.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

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

  16. #16
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    706

    Re: csv FORMAT FILE PROBLEMS

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

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

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

  18. #18
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

    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

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    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
    C
    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
    Last edited by AliGW; 08-08-2020 at 12:36 PM.

  20. #20
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

    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#".

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

    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.
    Last edited by AliGW; 08-08-2020 at 01:04 PM.

  22. #22
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

    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

  23. #23
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

    No difference after adding the spaces.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,436

    Re: csv FORMAT FILE PROBLEMS

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

  25. #25
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    2016
    Posts
    361

    Re: csv FORMAT FILE PROBLEMS

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Problems with Pasting data from an .csv-file into an .xlsm-file
    By lassebasse in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-30-2019, 09:02 AM
  2. Replies: 3
    Last Post: 09-21-2017, 08:41 AM
  3. Replies: 4
    Last Post: 10-23-2013, 12:54 AM
  4. [SOLVED] Format Problems
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2012, 02:20 PM
  5. How to: Open file, format data, save file, close file and repeat.
    By thexeber in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-11-2010, 12:56 PM
  6. appending to .txt file format problems
    By pikapika13 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2006, 12:51 PM
  7. Excel 2007 File Problems (Opening New Format)
    By Grazen in forum Excel General
    Replies: 3
    Last Post: 07-21-2006, 11:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1