+ Reply to Thread
Results 1 to 14 of 14

How to properly edit a csv file

  1. #1
    Registered User
    Join Date
    02-03-2021
    Location
    Bulgaria
    MS-Off Ver
    Home and Business 2019
    Posts
    53

    How to properly edit a csv file

    Hello,

    I have various csv file in which a lot of columns have numbers starting with 0 (009 or 00231231 or whatever else). If I open the csv file directly, automatically it cuts all the zero in front of that number. If I made any changes to this file and I save back the file, I lose the formatting for all the numbers starting with 0.

    The only solution I know it is to create an xlsx file and import the csv file and during the import to set as string these columns. But this operation generates a table and a query. I do not want this.

    I would like to modify directly the csv file if possible.

    In my specific case, what I would like to achieve is to delete various columns from the csv file before and then import the truncated file in excel with the data import function.

    How to do it?

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: How to properly edit a csv file

    If you want to delete columns, then probably the Power Query is the best option within Excel.

    Looking outside Excel, probably an easy one would be to use a pure text editor, which allows rectangular selection (most like Notepad don't have this). For instance: Notepad++ see https://notepad-plus-plus.org/
    Best Regards,

    Kaper

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to properly edit a csv file

    Have you tried copying and pasting?

    Can you provide a small sample .csv file for us to test (desensitised)?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    02-03-2021
    Location
    Bulgaria
    MS-Off Ver
    Home and Business 2019
    Posts
    53

    Re: How to properly edit a csv file

    Hello,

    attached you can find a small example. The first two columns must be text in order to be seen correctly.

    The third one, you can see how the code will look right (if you concatenate the first two).
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to properly edit a csv file

    Have you tried just opening the .csv in Excel (not importing) and using Text to Columns on the Data ribbon? Select TEXT in the final dialog.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-03-2021
    Location
    Bulgaria
    MS-Off Ver
    Home and Business 2019
    Posts
    53

    Re: How to properly edit a csv file

    It does not work. First you can chose only one column. It returns me error if I chose more columns. Second it adds only a ' as symbol in front of the number

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to properly edit a csv file

    It works on the sample file you provided, as I proved. I can't comment on anything other than that.

    Perhaps if you provide a more realistic sample file, we might be able to offer more targeted help.

    Please don't forget 'thank you', even if a suggested solution doesn't work. People are giving of their own time freely to help you.

    First you can chose only one column.
    There is only one column in your sample .csv file. Did you OPEN the file or IMPORT it? You need to do the former (OPEN).
    Last edited by AliGW; 01-17-2022 at 07:20 AM.

  8. #8
    Registered User
    Join Date
    02-03-2021
    Location
    Bulgaria
    MS-Off Ver
    Home and Business 2019
    Posts
    53

    Re: How to properly edit a csv file

    Hello, and thank you in advance

    If you can see only one column is probably because in the windows setting the delimiter is not ";"

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to properly edit a csv file

    I am not importing the .csv - I am opening it directly. That's the difference.

  10. #10
    Registered User
    Join Date
    02-03-2021
    Location
    Bulgaria
    MS-Off Ver
    Home and Business 2019
    Posts
    53

    Re: How to properly edit a csv file

    I am not telling that you are importing the file. I am speaking about the windows regional setting. Depending on the region you are, the separator in the regional setting is different. My file was created using the regional setting set on my pc that probably is different from what you have on your computer.

    Follow this to check :

    Close the Excel application
    Click on the Windows/Start button
    Select Control Panel
    Select Region and Language
    Click on Formats Tab
    Click on Additional Settings
    Locate the List separator
    set ";" as separetor

    After that open again the csv and you will see all the columns.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to properly edit a csv file

    OK - I can't help you, sorry. Good luck.

  12. #12
    Registered User
    Join Date
    02-03-2021
    Location
    Bulgaria
    MS-Off Ver
    Home and Business 2019
    Posts
    53

    Re: How to properly edit a csv file

    Thank you anyway

  13. #13
    Registered User
    Join Date
    02-03-2021
    Location
    Bulgaria
    MS-Off Ver
    Home and Business 2019
    Posts
    53

    Re: How to properly edit a csv file

    Hello,

    with power query what I can do is to import the csv in the excel file. Delete/edit the columns that I want. Then save the file as csv. Unfortunatelly the original csv file was generated using utf8 format. Excell automatically convert the file to cyrillic format (in the file there are cyrillic fonts). So it is not the best solution.

    I am already using notepad++ as editor. It is very powerful. I check for rectangular selection as you suggested me as well as column selection..unfortunatelly I didn't find a way to make it work.
    The rectancular selection is ok if the columns have the same lenght. If the data in every record/row of a specified column has different lenght, the rectangular selection cuts some text or viceversa add additional test from the nearby column. Do you have any idea how to deal with it?

  14. #14
    Registered User
    Join Date
    01-09-2022
    Location
    Monterrey Mexico
    MS-Off Ver
    office 365
    Posts
    13

    Re: How to properly edit a csv file

    I had a similar problem.
    First I used power query to transform the data. After that, used VBA to open notepad and copy-paste the information and then save it with the encoding that you need.
    Look for information about "Open Filename For Output As #1" in VBA. In my case I needed to create 400 csv/txt files so it was a loop that filtered information and then created the file in utf8 format

+ 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. Browse for a file edit the file then attach it to an email
    By Bobbbo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2016, 11:50 PM
  2. VBA edit to Update File Automatically based on Name and Date of File
    By Ngutierr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2015, 01:15 PM
  3. Copy, edit and save workbook to same location as source data file, not macro file.
    By Jasonhouse in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2014, 09:01 AM
  4. CSV file not showing the db results properly
    By philweb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-24-2008, 04:27 PM
  5. HELP! file csv is not read properly
    By justix in forum Excel - New Users/Basics
    Replies: 17
    Last Post: 01-06-2007, 06:49 PM
  6. Importing text file, only option to edit existing file
    By smokey99 in forum Excel General
    Replies: 8
    Last Post: 04-26-2006, 04:08 PM
  7. Replies: 1
    Last Post: 01-24-2006, 12:10 PM

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