Search results for: Vba convert txt to csv

Below are our best 36 results for vba convert txt to csv, updated recently.

excelforum.com

to manually open each file and delete the spaces. What I did: I open csv file as input, use trim (string) to get rid of the extra. spaces. then i write each row to a .txt file. if i open the txt file using NotePad then do SaveAs to a csv file. it. works fine. when user reviewing it from NotePad, they still see " ".

plex.page

From text file to Excel, click on the Export button Select the file source and click the Export button. To import text from a Text file into an Excel spreadsheet, click the button below. To import data from a text file to a text file, click the button button. To retrieve the data, you'll have to open the text file from where you want to access it.

mrexcel.com

ADVERTISEMENT. I agree with Ruddles but if you want to rename the extension on the files then the following bit of VBA will work: Code: Sub Rename_Files () Const FROM_F = "csv": Const TO_F = "txt" Dim fldr As String Dim strFile As String With Application.FileDialog (msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath ...

People also ask
More FAQs for vba convert txt to csv
  • How do I convert text to a CSV file?

    These tab characters organize the text into tabular data. You can convert the file to a CSV (comma-separated values) file using a spreadsheet application, such as Microsoft Excel or LibreOffice Calc. Using Microsoft Excel. Open Microsoft Excel. Click the File tab in the Ribbon menu and click the Open option.

    Updated: 11/13/2018 by Computer Hope

    Microsoft Excel logo

    A tab-delimited text file is a text file whose units of text are separated by a tab character. These tab characters organize the text into tabular data. You can convert the file to a CSV (comma-separated values) file using a spreadsheet application, such as Microsoft Excel or LibreOffice Calc.

    • Using Microsoft Excel.
    • Using LibreOffice Calc.

    Using Microsoft Excel

    1. Open Microsoft Excel.
    2. Click the File tab in the Ribbon menu and click the Open option.
    3. Click the Browse option to bring up the Open or Browse window.
    4. Next to the File Name field, click the File Type drop-down menu and select the Text Files option.
    5. Navigate to the location of the text file, select it, and click the Open button.
    6. In the Text Import Wizard, the Delimited option should already be selected (shown below). Click the Next button to go to Step 2 of the wizard.

    Microsoft Excel Text Import Wizard Step 1

    Note

    If the text file has a header row (row that identifies the name of each column of data), click the check box for My data has headers.

    1. Make sure the Tab option is checked and validate the data is separated correctly in the Data preview section.

    Microsoft Excel Text Import Wizard Step 2

    1. To format each column of data, like text, date, or numeric, click the Next button and select the format type for each column of data. If you don't need to format each column of data, click the Finish button.
    2. The text file data is converted to spreadsheet format. Again, click the File tab in the Ribbon menu and select the Save As option.
    3. In the Save As window, select the CSV (Comma delimited) (*.csv) option in the Save as type drop-down menu.
    4. Type a name for the CSV file in the File name field, navigate to where you want to save the file, then click the Save button.

    The file is saved as a CSV file, which is a text file that uses commas as delimiters. It can be opened in a spreadsheet application or in any text editor.

    Using LibreOffice Calc

    LibreOffice is a free, open-source office productivity software suite that runs on many operating systems, including Linux, Microsoft Windows, and macOS. Its spreadsheet application is LibreOffice Calc.

    Follow these steps to open a tab-delimited text file in LibreOffice Calc, view it as a spreadsheet, and save it as a CSV file.

    1. Open LibreOffice Calc.
    2. From the File menu, select Open and select your tab-delimited text file.

    LibreOffice Calc file menu

    1. Once the Text Import dialog box opens, by default, it interprets tabs, commas, and semicolons as column delimiters, and newlines as row delimiters.

    LibreOffice Calc Text Import options dialog

    1. Unselect Comma and Semicolon.

    Unselecting the comma and semicolon delimiter options in the LibreOffice Calc Text Import options dialog

    1. Click OK. Your data loads into a spreadsheet.

    Data loaded into spreadsheet

    1. You can now select Save As from the file menu, and choose Text CSV (.csv) as the File Type.

    Saving as CSV

    1. To save the file, click Save.

    Additional information

    How to change a tab-delimited text file to a CSV file
  • Can I use a VBA to convert a CSV file to excel?

    When you use a Microsoft Visual Basic for Applications (VBA) macro to convert a Comma-Separated Values (CSV) text file into a Microsoft Office Excel workbook (*.xls), the format of dates that are converted into your Excel workbook may not be correct.
    • Article
    • 2 minutes to read
      • helenclu.png?size=32h
      • simonxjx.png?size=32s
      • CrystalThomasMS.png?size=32C
      • AmandaAZ.png?size=32A
    • Applies to:
      Microsoft Excel

    Note

    Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

    When you use a Microsoft Visual Basic for Applications (VBA) macro to convert a Comma-Separated Values (CSV) text file into a Microsoft Office Excel workbook (*.xls), the format of dates that are converted into your Excel workbook may not be correct.

    For example, in your CSV file, dates may be in the format of:

    dd/mm/yyyy

    When you run the following macro to convert your CSV text file into Excel,

    Sub test()
    
    Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _
          TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4)
    
    End Sub
    

    dates may be converted in the following format:

    mm/dd/yyyy

    Resolution

    Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To resolve this problem, add the local:=true parameter to your VBA macro as in the following example:

    Sub test()
    
    Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _
          TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4), Local:=True
    
    End Sub
    
    Date format that's convert a CSV text file by using VBA is
  • How to convert Excel worksheet into CSV comma delimited text file?

    We can use VBA to convert and Save the Excel File as CSV file. This example macro will help us to know how to convert Excel Worksheet into a CSV Comma delimited text file. This can be used in Excel 2003,2007,2010,2013. vba code to convert excel to csv- Syntax. Here is the example Excel VBA Syntax to convert the Excel to CSV.

    fusion-row -->
    vba code to convert excel to csv
  • How do I save a file as a CSV?

    Type a name for the CSV file in the File name field, navigate to where you want to save the file, then click the Save button. The file is saved as a CSV file, which is a text file that uses commas as delimiters.

    Updated: 11/13/2018 by Computer Hope

    Microsoft Excel logo

    A tab-delimited text file is a text file whose units of text are separated by a tab character. These tab characters organize the text into tabular data. You can convert the file to a CSV (comma-separated values) file using a spreadsheet application, such as Microsoft Excel or LibreOffice Calc.

    • Using Microsoft Excel.
    • Using LibreOffice Calc.

    Using Microsoft Excel

    1. Open Microsoft Excel.
    2. Click the File tab in the Ribbon menu and click the Open option.
    3. Click the Browse option to bring up the Open or Browse window.
    4. Next to the File Name field, click the File Type drop-down menu and select the Text Files option.
    5. Navigate to the location of the text file, select it, and click the Open button.
    6. In the Text Import Wizard, the Delimited option should already be selected (shown below). Click the Next button to go to Step 2 of the wizard.

    Microsoft Excel Text Import Wizard Step 1

    Note

    If the text file has a header row (row that identifies the name of each column of data), click the check box for My data has headers.

    1. Make sure the Tab option is checked and validate the data is separated correctly in the Data preview section.

    Microsoft Excel Text Import Wizard Step 2

    1. To format each column of data, like text, date, or numeric, click the Next button and select the format type for each column of data. If you don't need to format each column of data, click the Finish button.
    2. The text file data is converted to spreadsheet format. Again, click the File tab in the Ribbon menu and select the Save As option.
    3. In the Save As window, select the CSV (Comma delimited) (*.csv) option in the Save as type drop-down menu.
    4. Type a name for the CSV file in the File name field, navigate to where you want to save the file, then click the Save button.

    The file is saved as a CSV file, which is a text file that uses commas as delimiters. It can be opened in a spreadsheet application or in any text editor.

    Using LibreOffice Calc

    LibreOffice is a free, open-source office productivity software suite that runs on many operating systems, including Linux, Microsoft Windows, and macOS. Its spreadsheet application is LibreOffice Calc.

    Follow these steps to open a tab-delimited text file in LibreOffice Calc, view it as a spreadsheet, and save it as a CSV file.

    1. Open LibreOffice Calc.
    2. From the File menu, select Open and select your tab-delimited text file.

    LibreOffice Calc file menu

    1. Once the Text Import dialog box opens, by default, it interprets tabs, commas, and semicolons as column delimiters, and newlines as row delimiters.

    LibreOffice Calc Text Import options dialog

    1. Unselect Comma and Semicolon.

    Unselecting the comma and semicolon delimiter options in the LibreOffice Calc Text Import options dialog

    1. Click OK. Your data loads into a spreadsheet.

    Data loaded into spreadsheet

    1. You can now select Save As from the file menu, and choose Text CSV (.csv) as the File Type.

    Saving as CSV

    1. To save the file, click Save.

    Additional information

    How to change a tab-delimited text file to a CSV file
forum.ozgrid.com

Oct 18th 2007. #4. Re: Change Txt File Extension To Csv. File>Open. Right click on the file and use Rename. [SIZE="2"] [center] Merge Or Convert Excel | Trading Add-ins For Excel | Convert Excel Into Web Pages | Convert Databases Including Excel | Business Spreadsheets | Build Automatic Trading Models in Excel [/SIZE] [/center] Report Content.

toolbox.com

It will detect the .csv file (regardless of it’s name), and will convert it to Tilde, and place the contents into a new file with the same name, but with .txt extension. Option Explicit. Private Sub Main () Dim sFileName As String. sFileName = GetFileName. Call ConvertCsvToTildeTxt (App.Path & “” & sFileName) DoEvents.

chandoo.org

Hi ALL:confused: I have a bunch of csv file from an external interface. The csv format is always same only data differ. Tried to achieve through recorded macro but unsuccessful. It converts three lines and stops. The headers always start at row 14 and part are found also in row 15 see csv...

whiterocksoftware.com

here is an example of running "mt940 To Csv" from command line to automate conversion:"C:\Program Files (x86)\WhiterockSoftware\mt940ToCsv\mt940ToCsv.exe" inputfile.mt940 outputfile.csv. Posted 22nd February 2020 by Unknown. Labels: bestand convert converter converteren csv erstellen format konwersja konwerter mt940 mt940 to csv swift to …

convert2f.com

Activate the worksheet that you want to import the CSV file, then click Data > From Text, see screenshot: 2. In the Import Text File w indow, please specify the CSV file that you want to import, see screenshot: More Info At www.extendoffice.com ››. Vba Change Date Format . Zip File Size Reducer Online .

stackoverflow.com

08-05-2017 · convert a txt (bibtex) file to csv with vba excel. Ask Question Asked 4 years, 6 months ago. ... Viewed 713 times -2 0. I would like to ask for your help with the following: i want to convert txt file (bibtex) to csv with vba here is the text that i want to convert : @inproceedings{orfila2015ecodriving, title={Ecodriving performances of human ...

08-05-2017Array
Converting txt files to CSV files

Dim fPath As String: fPath = "C:\Import\" 'path to CSV files, include the final \. Dim fCSV As String. Application.ScreenUpdating = False. fCSV = Dir (fPath & "*.csv") 'start the CSV file listing. Do While Len (fCSV) > 0. 'open a CSV file. Set wbCSV = Workbooks.Open (fPath & fCSV) 'insert col A and add CSV name.

A CSV file is a text file already, just formatted with commas.
Not sure what you're trying to accomplish as I can't understand your problem description.
A CSV file is a text file already, just formatted with commas.
Not sure what you're trying to accomplish as I can't understand your problem description.
Actually My end object is to import all the text file to an excel sheet. I have a VBA code which imports CSV files to an excel sheet but the data should reside in A1 cell of all CSV files before importing. But in my case when I convert text files to CSV files then the data is splitted into multiple columns which is the only problem and VBA code is not working on such CSV files. For reference, I VBA code is given below: Option Explicit Sub ImportCSVsWithReference() 'Author: Jerry 'Date: 10/16/2010 ' Import all CSV files from a folder into a single sheet ' adding a field in column A listing the CSV filenames Dim wbCSV As Workbook Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("MasterCSV") Dim fPath As String: fPath = "C:\Import\" 'path to CSV files, include the final \ Dim fCSV As String Application.ScreenUpdating = False fCSV = Dir(fPath & "*.csv") 'start the CSV file listing Do While Len(fCSV) > 0 'open a CSV file Set wbCSV = Workbooks.Open(fPath & fCSV) 'insert col A and add CSV name Columns(1).Insert xlShiftToRight Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name 'copy date into master sheet and close source file ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1) wbCSV.Close False 'ready next CSV fCSV = Dir Loop Application.ScreenUpdating = True

End Sub

social.msdn.microsoft.com

Hello Team, I am trying to convert multiple .txt file having tab delimited to .csv file. However the code that I am using is also considering ',' as delimited in addition to 'tab'. I have posted the code below and it would be great help if somebody could help me rectify the below code to accept ... · Hi, This is the forum to discuss questions and ...

converting a text file to CSV

converting a text file to CSV How to change a text file with spaces or comma delimiters to a CSV or Excel file This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread. ... Manually change the file extension on the file from .txt to .csv Then you can open the file with Excel . . .

Hi Ralph, Open File Explorer On the View Ribbon, check the box marked 'File Name Extensions' Manually change the file extension on the file from .txt to .csv

Then you can open the file with Excel . . .

___________________________________________________________________Power to the Developer!

MSI GV72 - 17.3", i7-8750H (Hex Core), 32GB DDR4, 4GB GeForce GTX 1050 Ti, 256GB NVMe M2, 2TB HDD

Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.

Details (required):

250 characters remaining

6 people found this reply helpful

·

Was this reply helpful?

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

rjsherman.com

10-06-2014 · For instance, if you are importing a text file with city, states, and zip codes, you may run into an issue during your data review because most likely, …

10-06-2014

Botching a data import can ruin all subsequent data analysis. Raw data import processes are very sensitive and finicky based on the type of data that is being imported and the structure of that data.  For instance, if you are importing a text file with city, states, and zip codes, you may run into an issue during your data review because most likely, Excel will have dropped the leading 0’s from the zip codes. Ugh.

Let’s say that data import is a step in a larger process rather than the end game and you’re building a sales forecasting model to analyze monthly raw sales data, and then make predictions about future sales goals. Every month, there is new data released, and therefore, everytime the end user opens the workbook and executed the macro, it would be a best practice to prompt them to import new data.

When you are importing data into excel from an external data source, you have a couple of options:

  • Option 1: Opening the raw data file through the Excel File -> Open process which enables you to select how to each column is integrated and opens the file in a new workbook.  This will walk you through steps defining for Excel how the data file is structured including the delimiters used, where to start the import, characters used for delimiting, and finally the column by column data format assignment
    • This process is fairly consistent across Excel’s external data import tools, however the open functionality does not readily translate into a macro unless you were to have a separate file open that could then be referenced to copy the data values. This would be prone to errors and require a savvy end user
  • Option 2: Use QueryTable to import the external data directly into the existing workbook
    • The QueryTable process is predicated on the raw data not changing in format or structure.  If your data is prone to changes in structure, you may need to go through a couple of additional steps to define some of the values that I will explain below

Given the robustness and customization options for QueryTable, this is my preferred choice.

I have broken my larger subroutine (ImportData) into two parts:

  1. Part 1: Import File Selection (the process of allowing the user to choose which file to import)
  2. Part 2: QueryTable set up for the selected file (the process of Excel processing the selected file and placing the formatted file into the existing workbook)

Part 1: Import File Selection

The below first half of the subroutine opens up a FileDialog box that enables the user to select one txt file, and then at the bottom of this excerpt of code, there are some helper functions that for instance: extract just the file name. This is helpful as we move to the QueryTable portion because setting variable such as the file path, and the file name then makes the QueryTable code more dynamic.

Sub ImportData()
    Dim txtFileNameAndPath As String
    Dim ImportingFileName As String
    Dim SheetName As Worksheet
    Dim fd As Office.FileDialog

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
		'Enable this option if you want the use to be able to select multiple files
        .AllowMultiSelect = False

        'This sets the title of the dialog box.
        .Title = "Please select the file."

        'Sets the associated filters for types of files
        .Filters.Clear
        .Filters.Add "txt", "*.txt"
        .Filters.Add "All Files", "*.*"

        ' Show the dialog box. If the .Show method returns True, the
        ' user picked at least one file. If the .Show method returns
        ' False, the user clicked Cancel.
        If .Show = True Then
          txtFileNameAndPath = .SelectedItems(1)
        Else
            MsgBox "Please start over.  You must select a file to import"
			'You don't want the sub continuing if there wasn't a file selected
            Exit Sub
        End If
    End With

	'Extracts only the file name for reference later
    ImportingFileName = Right(txtFileNameAndPath, _
		Len(txtFileNameAndPath) - InStrRev(txtFileNameAndPath, "\"))

When you are looking to implement this code in your own workbook, it would be good to make sure that the user has selected the correct file.  For instance, you can search for words within the file name, you can verify the import afterwards, etc. The end goal is to ensure that the user doesn’t import data that can’t be processed.  In my implementation, I also called a MsgBox immediately before calling this subroutine that explained to the user what was about to happen and the types of file the user should select to import.

Part 2: QueryTable Set Up

The second part of this subroutine takes the selected file, and then leverages QueryTable to consistently import the file.

Couple things to note about the below code:

  • The worksheet where the data will ultimately reside needs to be the active sheet prior to QueryTable
    • The Destination and the Active worksheet should be the same sheet
  • Values such as TextFileTextQualifier, TextFileStartRow for instance, should be updated based on the way your data is structured
  • TextFileColumnDataTypes will need an entry for every column that is being imported.  Values should be used to differentiate between general, text, etc.
    'You need to be on the active worksheet for the below code to work
    Worksheets("Sheet 1").Activate 

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & txtFileNameAndPath _
        , Destination:=Worksheets("Sheet 1").Range("$A

")) .Name = "ImportingFileName" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 2 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False 'Fill in the array in TextFileColumnDataTypes according to 'the type of data types that you are looking to import .TextFileColumnDataTypes = Array(1,1,1,1,1,1,1,1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub

I found the best way to get started with QueryTables, is to record a macro as you go through the data import process and then edit the macro accordingly.  This will save you some time as values such as TextFileColumnDataTypes will be automatically populated.

If you have any feedback or additional ideas to make this a more seamless process, please feel free to share.

analysistabs.com

19-01-2015 · We can use VBA to convert and Save the Excel File as CSV file. This example macro will help us to know how to convert Excel Worksheet into a CSV Comma delimited text file. This can be used in Excel 2003,2007,2010,2013. vba code to convert excel to csv- Syntax. Here is the example Excel VBA Syntax to convert the Excel to CSV.

19-01-2015

fusion-row -->
Date format that's convert a CSV text file by using VBA is ...

For example, in your CSV file, dates may be in the format of: dd/mm/yyyy. When you run the following macro to convert your CSV text file into Excel, Sub test() Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _ TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4) End Sub dates may be converted in the following format:

  • Article
  • 2 minutes to read
    • helenclu.png?size=32h
    • simonxjx.png?size=32s
    • CrystalThomasMS.png?size=32C
    • AmandaAZ.png?size=32A
  • Applies to:
    Microsoft Excel

Note

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

When you use a Microsoft Visual Basic for Applications (VBA) macro to convert a Comma-Separated Values (CSV) text file into a Microsoft Office Excel workbook (*.xls), the format of dates that are converted into your Excel workbook may not be correct.

For example, in your CSV file, dates may be in the format of:

dd/mm/yyyy

When you run the following macro to convert your CSV text file into Excel,

Sub test()

Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _
      TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4)

End Sub

dates may be converted in the following format:

mm/dd/yyyy

Resolution

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To resolve this problem, add the local:=true parameter to your VBA macro as in the following example:

Sub test()

Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _
      TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4), Local:=True

End Sub
social.msdn.microsoft.com

All replies. This is a VB Dot Net forum, not VB6, but that said insofar as converting a text file to a .csv, a CSV actually is just a text file with the .csv extension. Obviously the data in the text file would have to conform to being as comma-separated values, but if it is, it's nothing more then renaming the file.

convertio.co

Convert to TXT. txt. Plaintext Text File. TXT is a file format that contains the text, organized in rows. Text files opposed binaries that contain data that is not intended for interpretation as a text (encoded sound or image). The text file can contain both formatted and unformatted text. Since the very simple text files, they are frequently ...

[SOLVED] Converting a .txt to .csv - PowerShell

Then just import into Excel, using convert text to columns, without even using powershell. And since you are ultimately going to be using Excel to turn the CSV to an XLSX it shouldn't add too much over head to the process. You going to say you've got 100s of these to convert now aren't you :(I do not have any control over the report.

Wayne Andersen

Well, this isn't using PowerShell, but you could suck it into Excel using column positions and then put it out as a .csv.

Rob Dunn

A CSV file is technically a text file, but with a different name, so why not simply rename the file?

Does your text file actually contain a string that says "

" and "?" 

ranhalt

BloodHoundGang wrote:

I need to convert a text report...to a csv so I can send it in .xlsx format.


What?
Wayne Andersen

Rob Dunn wrote:

A CSV file is technically a text file, but with a different name, so why not simply rename the file?

Does your text file actually contain a string that says "

" and "?" 

Yeah, but don't you need to put the commas in?
Nick Holness

Where you have 'info' in the column data can this be "anything" so contain commas, spaces, etc i.e. free text? If so it's going to be really hard to separate out into a CSV file.

Do you have any control over the original report? If you do you could introduce a lesser used symbol (like ^) to separate the column data. Then just import into Excel, using convert text to columns, without even using powershell.

And since you are ultimately going to be using Excel to turn the CSV to an XLSX it shouldn't add too much over head to the process.

You going to say you've got 100s of these to convert now aren't you :(

BloodHoundGang

Wayne Andersen wrote:

Well, this isn't using PowerShell, but you could suck it into Excel using column positions and then put it out as a .csv.

The issue here is that I don't have a version of excel that will allow me to do text to columns. When I try to rename it doesn't format it to anything I'll be able to use without re-typing the whole thing. It's a rather large report and that would just take too long.

Rob Dunn wrote:

A CSV file is technically a text file, but with a different name, so why not simply rename the file?

Does your text file actually contain a string that says "

" and "?" 

No but it does contain a "header" and "body" that are formatted differently. This is why I'm trying to convert it so I can work with it in excel. At least if everything is comma delimited I might be able to work with it a little easier.

ranhalt wrote:

BloodHoundGang wrote:

I need to convert a text report...to a csv so I can send it in .xlsx format.


What?
I have a .txt that I need to be in a .xlsx and formatted the same as the .txt. I was hoping powershell might be able to help me.
Rob Dunn

Wayne Andersen wrote:

Rob Dunn wrote:

A CSV file is technically a text file, but with a different name, so why not simply rename the file?

Does your text file actually contain a string that says "

" and "?" 

Yeah, but don't you need to put the commas in?
Not necessarily, but I suppose this would be considered to be a 'TSV' file instead - and using tabs doesn't quite conform with some implementations of CSV...hmm.Back to OP: The example given here has 4 columns in the header, but 6 columns in the data.  Is this correct?
BloodHoundGang

Nick Holness wrote:

Where you have 'info' in the column data can this be "anything" so contain commas, spaces, etc i.e. free text? If so it's going to be really hard to separate out into a CSV file.

Do you have any control over the original report? If you do you could introduce a lesser used symbol (like ^) to separate the column data. Then just import into Excel, using convert text to columns, without even using powershell.

And since you are ultimately going to be using Excel to turn the CSV to an XLSX it shouldn't add too much over head to the process.

You going to say you've got 100s of these to convert now aren't you :(

I do not have any control over the report. It get's generated from a program and there is no option to change the format when it's generated. Unfortunately I only have a starter version of Excel and it doesn't have that feature. Not 100 but I do need to find a way to automate the process best I can.
Jpars

BloodHoundGang wrote:

Wayne Andersen wrote:

Well, this isn't using PowerShell, but you could suck it into Excel using column positions and then put it out as a .csv.

The issue here is that I don't have a version of excel that will allow me to do text to columns. When I try to rename it doesn't format it to anything I'll be able to use without re-typing the whole thing. It's a rather large report and that would just take too long.

Have you tried LibreOffice?  That should work.

BloodHoundGang

Rob Dunn wrote:

Wayne Andersen wrote:

Rob Dunn wrote:

A CSV file is technically a text file, but with a different name, so why not simply rename the file?

Does your text file actually contain a string that says "

" and "?" 

Yeah, but don't you need to put the commas in?
Not necessarily, but I suppose this would be considered to be a 'TSV' file instead - and using tabs doesn't quite conform with some implementations of CSV...hmm.Back to OP: The example given here has 4 columns in the header, but 6 columns in the data.  Is this correct?
It's more of a sample it actually has many more. I can always try to just get the body first and then go back and manually enter in the header.
ArtB

Are the text columns fixed width? If so, you can use the Substring method on the lines to separate them into the values and then combine them using New-Object PSObject -Property @{'Name1'=$Value1;'Name2'=$Value2} into an array that you would then be able to export to a CSV file.

If the text columns are variable width, then  it'll be much harder to accomplish.

Jpars

BloodHoundGang wrote:

Nick Holness wrote:

Where you have 'info' in the column data can this be "anything" so contain commas, spaces, etc i.e. free text? If so it's going to be really hard to separate out into a CSV file.

Do you have any control over the original report? If you do you could introduce a lesser used symbol (like ^) to separate the column data. Then just import into Excel, using convert text to columns, without even using powershell.

And since you are ultimately going to be using Excel to turn the CSV to an XLSX it shouldn't add too much over head to the process.

You going to say you've got 100s of these to convert now aren't you :(

I do not have any control over the report. It get's generated from a program and there is no option to change the format when it's generated. Unfortunately I only have a starter version of Excel and it doesn't have that feature. Not 100 but I do need to find a way to automate the process best I can.

Just pop it in LibreOffice and save an Excel file. Free and easy.

Jpars

You can also script something with LibreOffice to batch convert straight to an excel format.  No reason to go from txt to csv, just to import into Excel. 

BloodHoundGang

Jpars wrote:

BloodHoundGang wrote:

Nick Holness wrote:

Where you have 'info' in the column data can this be "anything" so contain commas, spaces, etc i.e. free text? If so it's going to be really hard to separate out into a CSV file.

Do you have any control over the original report? If you do you could introduce a lesser used symbol (like ^) to separate the column data. Then just import into Excel, using convert text to columns, without even using powershell.

And since you are ultimately going to be using Excel to turn the CSV to an XLSX it shouldn't add too much over head to the process.

You going to say you've got 100s of these to convert now aren't you :(

I do not have any control over the report. It get's generated from a program and there is no option to change the format when it's generated. Unfortunately I only have a starter version of Excel and it doesn't have that feature. Not 100 but I do need to find a way to automate the process best I can.

Just pop it in LibreOffice and save an Excel file. Free and easy.

I've actually tried it on another PC with 2013 and the issue is the formatting. It doesn't allow me to format it the way i need without re-typing almost everything
jessevas

What version of Excel are you using where you can't convert commas, tabs, or spaces to columns?

jessevas

Have you tried doing a find/replace to change your double-spacing "  " to comma ","?

Or, change it to "\t" for tab?

BloodHoundGang

jessevas wrote:

What version of Excel are you using where you can't convert commas, tabs, or spaces to columns?

Starter
cduff

Does the real file have the same number of header columns as data columns?

Does the data have spaces and commas?

These questions were asked above, but I didn't see a clear answer.

Also:

Is here always more that one space separating columns?

cduff

If this isn't something that you are going to do a lot, you might look into Notepad  .  Very good text editor for search and replace.  If you know a bit of regex, you can do miracles with it.

BloodHoundGang

cduff wrote:

Does the real file have the same number of header columns as data columns?

Does the data have spaces and commas?

These questions were asked above, but I didn't see a clear answer.

Also:

Is here always more that one space separating columns?

No it has more data columns than header columns. The header has commas the body has spaces. There are some that are almost tabbed and some with only a few spaces.
cduff

What header would you put on the extra data columns? Do the first data columns line up with the correct header columns?

BloodHoundGang

cduff wrote:

What header would you put on the extra data columns? Do the first data columns line up with the correct header columns?

Nothing lines up. It's a bit of a nightmare.
cduff

So, you would have to manually enter the headers.  It's like the header line shouldn't even be there.

BloodHoundGang

I'm getting more info ( trying to get an example). I don't see the point for the header. I am able to get the body formatted as needed. I think I might be able to just delete the header.

automateexcel.com

Convert Excel File to CSV. These two articles are the best places to start if you are looking to build a custom export to convert Excel to a CSV file. First off, If you aren’t interested in using VBA but would still like to convert excel to a CSV file, just click File->Save As-> and in the Save as Type dropdown choose *.CSV

convertseek.com

A comma-separated values (CSV) file is any file containing text that is separated with a comma, but can also be a file separated with any other character. A CSV file can be opened in any program, however, for most users, a CSV file is best viewed through a spreadsheet program such as Microsoft Excel, Open Office Calc or Google Docs.

support.microsoft.com

Import a text file by opening it in Excel. You can open a text file that you created in another program as an Excel workbook by using the Open command. Opening a text file in Excel does not change the format of the file — you can see this in the Excel title bar, where the name of the file retains the text file name extension (for example, .txt or .csv).

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less

There are two ways to import data from a text file with Excel: you can open it in Excel, or you can import it as an external data range. To export data from Excel to a text file, use the Save As command and change the file type from the drop-down menu.

There are two commonly used text file formats:

  • Delimited text files (.txt), in which the TAB character (ASCII character code 009) typically separates each field of text.

  • Comma separated values text files (.csv), in which the comma character (,) typically separates each field of text.

You can change the separator character that is used in both delimited and .csv text files. This may be necessary to make sure that the import or export operation works the way that you want it to.

Note: You can import or export up to 1,048,576 rows and 16,384 columns.

You can open a text file that you created in another program as an Excel workbook by using the Open command. Opening a text file in Excel does not change the format of the file — you can see this in the Excel title bar, where the name of the file retains the text file name extension (for example, .txt or .csv).

  1. Go to File > Open and browse to the location that contains the text file.

  2. Select Text Files in the file type dropdown list in the Open dialog box.

  3. Locate and double-click the text file that you want to open.

    • If the file is a text file (.txt), Excel starts the Import Text Wizard. When you are done with the steps, click Finish to complete the import operation. See Text Import Wizard for more information about delimiters and advanced options.

    • If the file is a .csv file, Excel automatically opens the text file and displays the data in a new workbook.

      Note: When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data. If you want more flexibility in converting columns to different data formats, you can use the Import Text Wizard. For example, the format of a data column in the .csv file may be MDY, but Excel's default data format is YMD, or you want to convert a column of numbers that contains leading zeros to text so you can preserve the leading zeros. To force Excel to run the Import Text Wizard, you can change the file name extension from .csv to .txt before you open it, or you can import a text file by connecting to it (for more information, see the following section).

You can import data from a text file into an existing worksheet.

  1. On the Data tab, in the Get & Transform Data group, click From Text/CSV.

  2. In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.

  3. In the preview dialog box, you have several options:

    • Select Load if you want to load the data directly to a new worksheet.

    • Alternatively, select Load to if you want to load the data to a table, PivotTable/PivotChart, an existing/new Excel worksheet, or simply create a connection. You also have the choice of adding your data to the Data Model.

    • Select Transform Data if you want to load the data to Power Query, and edit it before bringing it to Excel.

If Excel doesn't convert a particular column of data to the format that you want, then you can convert the data after you import it. For more information, see Convert numbers stored as text to numbers and Convert dates stored as text to dates.

You can convert an Excel worksheet to a text file by using the Save As command.

  1. Go to File > Save As.

  2. Click Browse.

  3. In the Save As dialog box, under Save as type box, choose the text file format for the worksheet; for example, click Text (Tab delimited) or CSV (Comma delimited).

    Note: The different formats support different feature sets. For more information about the feature sets that are supported by the different text file formats, see File formats that are supported in Excel.

  4. Browse to the location where you want to save the new text file, and then click Save.

  5. A dialog box appears, reminding you that only the current worksheet will be saved to the new file. If you are certain that the current worksheet is the one that you want to save as a text file, click OK. You can save other worksheets as separate text files by repeating this procedure for each worksheet.

    You may also see an alert below the ribbon that some features might be lost if you save the workbook in a CSV format.

For more information about saving files in other formats, see Save a workbook in another file format.

You can import data from a text file into an existing worksheet.

  1. Click the cell where you want to put the data from the text file.

  2. On the Data tab, in the Get External Data group, click From Text.

  3. In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.

    Follow the instructions in the Text Import Wizard. Click Help 404 image on any page of the Text Import Wizard for more information about using the wizard. When you are done with the steps in the wizard, click Finish to complete the import operation.

  4. In the Import Data dialog box, do the following:

    1. Under Where do you want to put the data?, do one of the following:

      • To return the data to the location that you selected, click Existing worksheet.

      • To return the data to the upper-left corner of a new worksheet, click New worksheet.

    2. Optionally, click Properties to set refresh, formatting, and layout options for the imported data.

    3. Click OK.

      Excel puts the external data range in the location that you specify.

If Excel does not convert a column of data to the format that you want, you can convert the data after you import it. For more information, see Convert numbers stored as text to numbers and Convert dates stored as text to dates.

You can convert an Excel worksheet to a text file by using the Save As command.

  1. Go to File > Save As.

  2. The Save As dialog box appears.

  3. In the Save as type box, choose the text file format for the worksheet.

    • For example, click Text (Tab delimited) or CSV (Comma delimited).

    • Note: The different formats support different feature sets. For more information about the feature sets that are supported by the different text file formats, see File formats that are supported in Excel.

  4. Browse to the location where you want to save the new text file, and then click Save.

  5. A dialog box appears, reminding you that only the current worksheet will be saved to the new file. If you are certain that the current worksheet is the one that you want to save as a text file, click OK. You can save other worksheets as separate text files by repeating this procedure for each worksheet.

  6. A second dialog box appears, reminding you that your worksheet may contain features that are not supported by text file formats. If you are interested only in saving the worksheet data into the new text file, click Yes. If you are unsure and would like to know more about which Excel features are not supported by text file formats, click Help for more information.

For more information about saving files in other formats, see Save a workbook in another file format.

The way you change the delimiter when importing is different depending on how you import the text.

  • If you use Get & Transform Data > From Text/CSV, after you choose the text file and click Import, choose a character to use from the list under Delimiter. You can see the effect of your new choice immediately in the data preview, so you can be sure you make the choice you want before you proceed.

  • If you use the Text Import Wizard to import a text file, you can change the delimiter that is used for the import operation in Step 2 of the Text Import Wizard. In this step, you can also change the way that consecutive delimiters, such as consecutive quotation marks, are handled.

    See Text Import Wizard for more information about delimiters and advanced options.

If you want to use a semi-colon as the default list separator when you Save As .csv, but need to limit the change to Excel, consider changing the default decimal separator to a comma - this forces Excel to use a semi-colon for the list separator. Obviously, this will also change the way decimal numbers are displayed, so also consider changing the Thousands separator to limit any confusion.

  1. Clear Excel Options > Advanced > Editing options > Use system separators.

  2. Set Decimal separator to , (a comma).

  3. Set Thousands separator to . (a period).

When you save a workbook as a .csv file, the default list separator (delimiter) is a comma. You can change this to another separator character using Windows Region settings. 

  1. In Microsoft Windows 10, right-click the Start button, and then click Settings.

  2. Click Time & Language, and then click Region in the left panel.

  3. In the main panel, under Regional settings, click Additional date, time, and regional settings.

  4. Under Region, click Change date, time, or number formats.

  5. In the Region dialog, on the Format tab, click Additional settings.

  6. In the Customize Format dialog, on the Numbers tab, type a character to use as the new separator in the List separator box.

  7. Click OK twice.

  1. In Microsoft Windows, click the Start button, and then click Control Panel.

  2. Under Clock, Language, and Region, click Change date, time, or number formats.

  3. In the Region dialog, on the Format tab, click Additional settings.

  4. In the Customize Format dialog, on the Numbers tab, type a character to use as the new separator in the List separator box.

  5. Click OK twice.

Note:  After you change the list separator character for your computer, all programs use the new character as a list separator. You can change the character back to the default character by following the same procedure.

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Import data from external data sources (Power Query)

404 image 404 image 404 image

social.msdn.microsoft.com

Answers. >> that macro should convert the PDF outputs to CSV format / Excel format. As far as I know, the Excel do not have the feature to read the PDF file, so you will need third party components to achieve this goal. According to your code, I think you were using Acrobat and if you have issues about third party components, I would recommend ...

CSV to TXT converter in dot net

08-07-2018 · Convert .csv(comma separated value) file to .txt file in vb.net. Here it will ignore comma in any particular field. I have made a windows application in vb.net which will take a .csv file as input using open file dialogue and give a .txt file as output in a folder which is chosen by the user using folder browser dialogue.

08-07-2018
e3a7b4c6ba6c9e9ea48f0093ebc4b82e?s=96&d=mm&r=g By Indibar Majhi

Convert .csv(comma separated value) file to .txt file in vb.net

Here it will ignore comma in any particular field. I have made a windows application in vb.net which will take a .csv file as input using open file dialogue and give a .txt file as output in a folder which is chosen by the user using folder browser dialogue.

The code is given below. You can add .csv extension checking in open file dialogue.

Different Type of String Operations in .NET using C#

Imports Microsoft.VisualBasic.FileIO

Public Class Form1
    Private Sub btn_start_Click(sender As Object, e As EventArgs) Handles btn_start.Click

        Dim filename As String = System.IO.Path.GetFileNameWithoutExtension(txt_source.Text)

        Dim tfp As New TextFieldParser(txt_source.Text)
        tfp.Delimiters = New String() {","}
        tfp.TextFieldType = FieldType.Delimited

        Dim file As System.IO.StreamWriter
        file = My.Computer.FileSystem.OpenTextFileWriter(txt_destination.Text + "\" + filename + ".txt", True)
        file.WriteLine("Name" & ControlChars.Tab & "Address" & ControlChars.Tab & "Phone No.")

        tfp.ReadLine() ' skip header
        While tfp.EndOfData = False
            Dim fields = tfp.ReadFields()

            file.WriteLine(fields(0) & ControlChars.Tab & fields(1) & ControlChars.Tab & fields(2))

        End While
        file.Close()
        MessageBox.Show("Conversion Successful", "Converter", MessageBoxButtons.OK, MessageBoxIcon.None)
    End Sub

    Private Sub btn_destination_Click(sender As Object, e As EventArgs) Handles btn_destination.Click
        If (FolderBrowserDialog1.ShowDialog() = DialogResult.OK) Then
            txt_destination.Text = FolderBrowserDialog1.SelectedPath
        End If
    End Sub

    Private Sub btn_source_Click(sender As Object, e As EventArgs) Handles btn_source.Click
        OpenFileDialog1.Title = "Please Select a File"
        OpenFileDialog1.InitialDirectory = "C:\"
        OpenFileDialog1.ShowDialog()
        OpenFileDialog1.RestoreDirectory = True
    End Sub

    Private Sub OpenFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
        Dim strm As System.IO.Stream
        strm = OpenFileDialog1.OpenFile()
        txt_source.Text = OpenFileDialog1.FileName.ToString()
    End Sub
End Class

Windows Form to select input and output location

Conversion Successful

Sample CSV

Sample TXT

Download Source Code:

CSV_to_TXT.zip

Convert Excel to CSV (comma delimited) and UTF-8 ...

To export an Excel file to CSV and preserve special characters, follow these steps: In your worksheet, click File > Save As or press F12. In the Save As dialog box, choose Unicode Text (*.txt) from the Save as type drop-down menu, and click Save. Open the txt document using your preferred text editor, for example Notepad.

The article explorers quick and efficient ways to export data from Excel to CSV keeping all special characters and foreign symbols intact. The methods work for all versions of Excel, from 365 to 2007.

Comma separated values (CSV) is a widely used format that stores tabular data (numbers and text) as plain text. Its popularity and viability are due to the fact that CSV files are supported by many different applications and systems at least as an alternative import/export format. The CSV format allows users to glance at the file and immediately diagnose the problems with data, change the delimiter, text qualifier, etc. All this is possible because a CSV document is plain text and an average user or even a novice can easily understand it without any learning curve.

CSV formats supported by Excel

Microsoft Excel allows saving a file in a few different CSV formats, and you may be curious to know the differences between them.

In essence, each CSV format saves data as comma-separated values but performs encoding in a slightly different way. For example, Mac uses a single carriage return () represented by \r for a line break, while Windows uses a combination of carriage return and line feed () represented by \r\n.

To correctly export data to other programs, Excel lets you choose the CSV formatting that best matches the program's expectations.

Here are the CSV options available in Excel 365. In your version, the list may look a little different.
CSV formats supported by Excel

CSV (comma delimited). This format saves data in a comma-separated text file that can be used in another Windows program or another version of the Windows operating system.

CSV (Macintosh). This format saves a workbook as a comma-separated file for use on the Mac operating system.

CSV (MS-DOS). Saves as a comma-separated document for use on the MS-DOS operating system.

CSV UTF-8 (comma delimited). It is Unicode Transformation Format 8-bit encoding that supports many special characters, including hieroglyphs and accented characters, and is backward compatible with ASCII. This format is recommended for files that contain any non-ASCII characters since the classic CSV format destroys them.

Besides CSV, there is one more format that may come in extremely handy for communicating with other programs.

Unicode Text (*.txt). This is a computing industry standard supported by almost all current operating systems including Windows, Macintosh, Linux and Solaris Unix. It can handle characters of almost all modern languages and some ancient ones.

Note. By strict definition, the CSV format implies separating values with commas. In reality, you may come across many other delimiters, a semicolon and tab being most common.

How to convert Excel file to CSV

When Excel data is to be transferred to some other application such as the Outlook Address book or Access database, the easiest way is to save your worksheet as a .csv file, and then import that file to another program.

To save an Excel file (.xlsx or .xls) in the CSV format, here are the steps you need to follow:

  1. In your workbook, switch to the target worksheet as only the active sheet will be converted.
  2. On the File tab, click Save As. Or press the F12 key to open the Save As dialog.
    Switch to the File tab and click Save As.
  3. In the Save As dialog box, pick the desired CSV format from the Save as type drop-down menu, On Windows, you'd choose either CSV (Comma delimited) or CSV UTF-8.
  4. Pick the destination folder and hit Save.
    Saving an Excel worksheet as a CSV (Comma delimited) file

In case your worksheet has any formatting, formulas, charts, shapes or other objects, you will be informed that some features in your workbook might be lost if you save it as CSV (Comma delimited). If that is Okay, click Yes to complete the conversion without the unsupported features.
Some features in your workbook might be lost if you save it as CSV.

Export Excel to CSV without destroying special characters

If your spreadsheet contains some special symbols, smart quotes or long dashes (e.g. inherited from a Word document), foreign characters (tildes, accents, etc.) or hieroglyphs, the method described above won't work.

The point is the saving to the CSV (comma delimited) format distorts any characters other than ASCII (American Standard Code for Information Interchange).

To keep non-ASCII characters undamaged, a document should be saved to a format that uses a Unicode character encoding. There exist two Unicode encoding forms: 8-bit (UTF-8) and 16-bit (UTF-16).

Before we move to the exporting steps, let us point out the key features of each encoding, so you can choose the format right for a particular case.

UTF-8 is a more compact encoding since it uses 1 to 4 bytes for each symbol. Generally, this format is recommended if ASCII characters are most prevalent in your file because most such characters are stored in one byte each. Another advantage is that a UTF-8 file containing only ASCII characters has absolutely the same encoding as an ASCII file.

UTF-16 uses 2 to 4 bytes to encode each symbol. However, a UTF-16 file does not always require more storage than UTF-8. For example, Japanese characters take 3 to 4 bytes in UTF-8 and 2 to 4 bytes in UTF-16. So, you may want to use UTF-16 if your data contains any Asian characters, including Japanese, Chinese or Korean. A noticeable disadvantage of this format is that it's not fully compatible with ASCII files and requires some Unicode-aware programs to display them. Please keep that in mind if you are going to import the resulting document somewhere outside of Excel.

Once you've decided on the format, the below instructions will walk you through the process.

How to convert Excel to CSV UTF-8

Suppose you have a worksheet with some foreign characters, Japanese names in our case:
Source data

Depending on the Excel version you are using, it may take 3 to 5 steps to convert this file to CSV keeping all special characters.

Export to CSV UTF-8 in Excel 2016 - 365

In Excel 2016 and later versions, you can save a file in the CSV format with UTF-8 encoding directly:

  1. In the target worksheet, click File > Save As or press the F12 key.
  2. In the Save As dialog box, select CSV UTF-8 (comma delimited) (*.csv) from the Save as type drop down.
  3. Click the Save button. Done!
    Exporting a sheet to CSV UTF-8 in Excel 2016 - 365

Convert to CSV UTF-8 in Excel 2013 - 2007

As older Excel versions do not support the UTF-8 encoding, you'll need to save your document in the Unicode Text format first, and then convert it to UTF-8.

To export an Excel file to CSV and preserve special characters, follow these steps:

  1. In your worksheet, click File > Save As or press F12.
  2. In the Save As dialog box, choose Unicode Text (*.txt) from the Save as type drop-down menu, and click Save.
    Save your Excel workbook as Unicode Text (*.txt) file.
  3. Open the txt document using your preferred text editor, for example Notepad.
    Note. Some simple text editors do not fully support all Unicode characters, therefore certain characters may be displayed as boxes. In most cases this won't affect the resulting file, so you can simply ignore this or use a more advanced text editor such as Notepad .
  4. Since the txt file is tab-delimited while we aim for a comma-separated file, replace the tabs with commas. Here's how:
    • Select any tab character, right click it and choose Copy from the context menu, or press the Ctrl C key combination.
      Copy the tab character in a txt document.
    • Press Ctrl H to open the Replace dialog box and paste the copied tab (Ctrl V) in the Find what field. After you've done this, the cursor will move rightwards indicating that the tab is pasted. Type a comma in the Replace with field and click Replace All.

      If you want a semicolon-delimited CSV, then replace tabs with semicolons.
      Replace all tabs with commas.

      If all done right, your resulting txt file should look similar to this:
      A comma separated text file

  5. In Notepad, click File > Save As and do three important changes:
    • In the File name box, change the .txt extension to .csv.
    • In the Save as type box, pick All files (*.*).
    • In the Encoding drop-down menu, select UTF-8 with BOM.

    When done, click the Save button.
    Change the encoding to UTF-8 with BOM.

Tips and notes:
  • The byte order mark (BOM) is a sequence of bytes at the start of a text stream that indicates Unicode encoding of a text document. In case of UTF-8 with BOM, the sequence 0xEF,0xBB,0xBF signals the reading program that UTF-8 encoding is used in the file. The Unicode standard permits but does not require the BOM in UTF-8. However, it is often crucial for correct UTF-8 recognition in Excel, especially when converting from Asian languages.
  • If your text editor does not allow changing the file extension, you can do that in Windows Explorer.

Now, you can open the CSV file in Excel and make sure all data is rendered correctly:
Excel sheet converted to CSV UTF-8

Note. If your file is intended for use in another application where the UTF-8 format is a must, do not make any edits nor save the file in Excel as this may cause encoding problems. If some data does not appear right in Excel, open the file in Notepad and fix the data there. Remember to save the file in the UTF-8 with BOM format again.

How to convert Excel file to CSV UTF-16

Exporting to CSV UTF-16 is done very much the same way as to CSV UTF-8:

  1. Save the workbook in the Unicode Text (*.txt) file format.
  2. Open the .txt document in a text editor such as Notepad and replace all tabs with commas.
  3. Change the file extension to .csv, make sure encoding is set to UTF-16 LE, and save the file.
    Converting Excel file to CSV UTF-16

Convert multiple Excel sheets to CSV

As already mentioned, Excel's Save As command is only able to convert an active worksheet. But what if your workbook contains a lot of different sheets, and you wish to turn them all into separate csv files? The only alternative suggested by Microsoft is saving each sheet under a different file name, which does not sound very inspiring, huh?
CSV does not support workbooks with multiple sheets.

So, is there a quick way to save multiple Excel sheets as CSV at once? Yes, it can be done with VBA.

The below code converts all worksheets in the current workbook to individual CSV files, one for each sheet. The file names are created from the workbook and sheet names (WorkbookName_SheetName.csv) and saved to the same folder as the original document.

Sub ConvertMultipleCSV()
	Dim ws As Worksheet
	Dim path As String

	Application.ScreenUpdating = False
	path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

	For Each ws In Worksheets
		ws.Copy
		ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
		ActiveWorkbook.Close False
	Next

	Application.ScreenUpdating = True
End Sub

Please keep in mind that the above code saves sheets in the CSV format. If there are any non-ASCII characters in your data, then you need to convert to UTF-8 CSV. This can be done by changing the file format from xlCSV to xlCSVUTF8. That is, you replace FileFormat:=xlCSV with FileFormat:=xlCSVUTF8.

Also, remember that CSV UTF-8 conversions are possible in Excel 2016 and higher.

The following guidelines will help you with adding the macro to your workbook: How to insert and run VBA code in Excel.

Apart from the methods described above, there exist a handful of other ways to convert Excel sheets to CVS. Below, I will share a couple of my favorite ones.

Excel to CSV via Google Spreadsheets

The use of Google Spreadsheets for .xlsx to .csv conversions seems a very simple workaround:

  1. In Google Spreadsheet, click File > Import.
    Import your Excel file to Google Spreadsheets
  2. Click Upload and drag-and-drop the file or select from your computer, and then click Import data.
    Upload the file and import data.
    Tip. If you have a relatively small dataset, it may be easier to copy/paste it directly in the spreadsheet.
  3. Go to the File menu > Download > Comma-separated values (.csv, current sheet).
    Download a Google spreadsheets as a comma-separated values .csv file.

Open the downloaded file in some text editor to make sure all the data is exported right.

Note. If your original Excel sheet contains special characters, the resulting CSV file may not display the characters correctly when opened in Excel, though it looks perfect in many other spreadsheet programs.

Save .xlsx to .xls and then convert to .csv

This method of converting Excel to CSV hardly needs any further explanations because the heading says it all :)

I came across this solution on some forum, cannot remember which exactly. To be honest, this method has never worked for me, but many users reported that special characters, which got lost when saving .xlsx directly to .csv, are preserved if to save a .xlsx file to .xls first, and and then save .xls as .csv as explained in How to convert Excel to CSV.

Anyway, you can try this method of exporting Excel to CSV on your side and if it works, this can be a real time-saver.

Convert Excel to CSV using OpenOffice

OpenOffice is an open-source suite of six applications. One of them is a spreadsheet app named Calc, which is really good at exporting spreadsheet data to the CSV format. In fact, it provides more options (encodings, delimiters, etc.) than Microsoft Excel and Google Sheets combined.

To convert your Excel file to CSV, follow these steps:

  1. Open your Excel document with OpenOffice Calc.
  2. Click File > Save as… and choose Text CSV (.csv) from the Save as type drop-down menu.
  3. Next, you will be asked to define encoding and delimiters. If your goal is the CVS format that correctly handles special characters, then choose:
    • Unicode (UTF-8) for Character set.
    • Comma for Field delimiter. If you need a semicolon-delimited csv file, then select semicolon (;) or whatever delimiter you want.
    • Quotation mark for Text delimiter.

    To complete the conversion, click OK.
    Convert an Excel file to CSV using OpenOffice.

It would be really nice if Excel provided similar options to perform fast and painless CSV conversions, wouldn't it?

These are the ways of converting Excel to CSV I am aware of. If you know other more efficient methods, please do share in comments. Thank you for reading!

You may also be interested in

VBA Macro To Convert PDF Files Into Different Format

So, if you have installed Adobe Professional on your computer and you need to convert some PDF files into Word files, for example, take a look at the code below. Apart from the primary procedure, I also wrote a loop to convert multiple PDF files at once.

A couple of days ago, Peter asked me on this post if it is possible to save a PDF file as an Excel table by using VBA. I started thinking about his question, and I thought it could be quite useful to automate the procedure of saving a PDF file into another format. The method that I wrote requires Adobe Professional to work. So, if you have installed Adobe Professional on your computer and you need to convert some PDF files into Word files, for example, take a look at the code below.

Apart from the primary procedure, I also wrote a loop to convert multiple PDF files at once. So, if you have a folder that contains PDF files, you can use this tool to obtain their file paths. Then you can use the attached workbook to convert them to a different format. The code actually uses the Save As command of Adobe Professional to save the files to the desired format. The available formats are:

Option Explicit
Option Private Module Sub SavePDFAsOtherFormat(PDFPath As String, FileExtension As String)
       'Saves a PDF file as another format using Adobe Professional.
       'By Christos Samaras
    'https://myengineeringworld.net/////
       'In order to use the macro you must enable the Acrobat library from VBA editor:
    'Go to Tools -> References -> Adobe Acrobat xx.0 Type Library, where xx depends
    'on your Acrobat Professional version (i.e. 9.0 or 10.0) you have installed to your PC.
       'Alternatively you can find it Tools -> References -> Browse and check for the path
    'C:Program FilesAdobeAcrobat xx.0Acrobatacrobat.tlb
    'where xx is your Acrobat version (i.e. 9.0 or 10.0 etc.).
       Dim objAcroApp      As Acrobat.AcroApp
    Dim objAcroAVDoc    As Acrobat.AcroAVDoc
    Dim objAcroPDDoc    As Acrobat.AcroPDDoc
    Dim objJSO          As Object
    Dim boResult        As Boolean
    Dim ExportFormat    As String
    Dim NewFilePath     As String
       'Check if the file exists.
    If Dir(PDFPath) = "" Then
        MsgBox "Cannot find the PDF file!" & vbCrLf & "Check the PDF path and retry.", _
                vbCritical, "File Path Error"
        Exit Sub
    End If
       'Check if the input file is a PDF file.
    If LCase(Right(PDFPath, 3)) <> "pdf" Then
        MsgBox "The input file is not a PDF file!", vbCritical, "File Type Error"
        Exit Sub
    End If
       'Initialize Acrobat by creating App object.
    Set objAcroApp = CreateObject("AcroExch.App")
       'Set AVDoc object.
    Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")
       'Open the PDF file.
    boResult = objAcroAVDoc.Open(PDFPath, "")
           'Set the PDDoc object.
    Set objAcroPDDoc = objAcroAVDoc.GetPDDoc
       'Set the JS Object - Java Script Object.
    Set objJSO = objAcroPDDoc.GetJSObject
       'Check the type of conversion.
    Select Case LCase(FileExtension) Case "eps": ExportFormat = "com.adobe.acrobat.eps" Case "html", "htm": ExportFormat = "com.adobe.acrobat.html" Case "jpeg", "jpg", "jpe": ExportFormat = "com.adobe.acrobat.jpeg" Case "jpf", "jpx", "jp2", "j2k", "j2c", "jpc": ExportFormat = "com.adobe.acrobat.jp2k" Case "docx": ExportFormat = "com.adobe.acrobat.docx" Case "doc": ExportFormat = "com.adobe.acrobat.doc" Case "png": ExportFormat = "com.adobe.acrobat.png" Case "ps": ExportFormat = "com.adobe.acrobat.ps" Case "rft": ExportFormat = "com.adobe.acrobat.rft" Case "xlsx": ExportFormat = "com.adobe.acrobat.xlsx" Case "xls": ExportFormat = "com.adobe.acrobat.spreadsheet" Case "txt": ExportFormat = "com.adobe.acrobat.accesstext" Case "tiff", "tif": ExportFormat = "com.adobe.acrobat.tiff" Case "xml": ExportFormat = "com.adobe.acrobat.xml-1-00" Case Else: ExportFormat = "Wrong Input"
    End Select  'Check if the format is correct and there are no errors. If ExportFormat <> "Wrong Input" And Err.Number = 0 Then  'Format is correct and no errors. 'Set the path of the new file. Note that Adobe instead of xls uses xml files. 'That's why here the xls extension changes to xml. If LCase(FileExtension) <> "xls" Then NewFilePath = WorksheetFunction.Substitute(PDFPath, ".pdf", "." & LCase(FileExtension)) Else NewFilePath = WorksheetFunction.Substitute(PDFPath, ".pdf", ".xml") End If  'Save PDF file to the new format. boResult = objJSO.SaveAs(NewFilePath, ExportFormat)  'Close the PDF file without saving the changes. boResult = objAcroAVDoc.Close(True)  'Close the Acrobat application. boResult = objAcroApp.Exit 'Inform the user that conversion was successfully. MsgBox "The PDf file:" & vbNewLine & PDFPath & vbNewLine & vbNewLine & _ "Was saved as: " & vbNewLine & NewFilePath, vbInformation, "Conversion finished successfully"            Else
               'Something went wrong, so close the PDF file and the application.
               'Close the PDF file without saving the changes.
        boResult = objAcroAVDoc.Close(True)
               'Close the Acrobat application.
        boResult = objAcroApp.Exit
               'Inform the user that something went wrong.
        MsgBox "Something went wrong!" & vbNewLine & "The conversion of the following PDF file FAILED:" & _
        vbNewLine & PDFPath, vbInformation, "Conversion failed"     End If
           'Release the objects.
    Set objAcroPDDoc = Nothing
    Set objAcroAVDoc = Nothing
    Set objAcroApp = Nothing
       
End Sub

Here is the macro that loops through all the file paths in column “B” of the sheet “Paths” and converts the PDF files to a different file type. The macro ExportAllPDFs uses the SavePDFAsOtherFormatNoMsg macro (similar to SavePDFAsOtherFormat macro) but without the message boxes.

Sub ExportAllPDFs()
        'Convert all the PDF files that their paths are on column B of
    'the worksheet "Paths" into a different file format.
    'By Christos Samaras
    'https://myengineeringworld.net/////
        Dim FileFormat As String
    Dim LastRow As Long
    Dim i As Integer
     'Change this according to your own needs. 'Available formats: eps html, htm jpeg, jpg, jpe jpf, jpx, jp2, 'j2k, j2c, jpc, docx, doc, png, ps, rft, xlsx, xls, txt, tiff, tif and xml. 'In this example the PDF file will be saved as text file.
    FileFormat = "txt"
        If FileFormat = "" Then
        shPaths.Range("B2").Select
        MsgBox "There are no file paths to convert!", vbInformation, "File paths missing"
        Exit Sub
    End If     shPaths.Activate
        'Find the last row.
    With shPaths
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
        'Check that there are available file paths.
    If LastRow < 2 Then
        shPaths.Range("B2").Select
        MsgBox "There are no file paths to convert!", vbInformation, "File paths missing"
        Exit Sub
    End If
        'For each cell in the range "B2:B" & last row convert the pdf file
    'into different format (here to text - txt).
    For i = 2 To LastRow
        SavePDFAsOtherFormatNoMsg Cells(i, 2).Value, FileFormat
    Next i
        'Inform the user that conversion finished.
    MsgBox "All files were converted successfully!", vbInformation, "Finished"
    
End Sub

Download it from here

Download.jpg

The file can be opened with Excel 2007 or newer. Please, remember to enable macros before using it.

Update

Update 18/7/2013

The previous workbook was not convenient for converting multiple PDF files, so I decided to change the two macros a little bit. You can find the new VBA code below, as well as the new workbook in the download section that follows.

Option Explicit
Option Private Module Sub ExportAllPDFs()
       '----------------------------------------------------------------
    'Converts all the PDF files that their paths are in column A of
    'the worksheet "Convert PDF Files" into a different file format,
    'based on the value in column B (extension).
       'By Christos Samaras
    'Date: 18/07/2013
    'https://myengineeringworld.net/////
    '----------------------------------------------------------------     Dim LastRow As Long
    Dim i As Integer
       shPaths.Activate
       'Find the last row.
    With shPaths
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
       'Check that there are available file paths.
    If LastRow < 2 Then
        shPaths.Range("A2").Select
        MsgBox "There are no file paths to convert!", vbInformation, "File paths missing"
        Exit Sub
    End If
           'Checking for errors before conversion.
    For i = 2 To LastRow
           'Check if the file extensions are not empty.
        If Cells(i, 2).Value = "" Then
            shPaths.Cells(i, 2).Select
            MsgBox "Please select an output format from the dropdown list!", vbCritical, "File paths missing"
            Exit Sub
        End If
               'Check if the file exists.
        If Dir(shPaths.Cells(i, 1).Value) = "" Then
            shPaths.Cells(i, 1).Select
            MsgBox "The file path is not valid!", vbCritical, "File path error"
            Exit Sub
        End If
           'Check if the input file is a PDF file.
        If LCase(Right(shPaths.Cells(i, 1).Value, 3)) <> "pdf" Then
            shPaths.Cells(i, 1).Select
            MsgBox "The file is not a pdf file!", vbCritical, "No pdf file"
            Exit Sub
        End If
           Next i
       'For each cell in the range "A2:A" & last row convert the pdf file
    'into different format according to the "B2:B" & last row value.
    For i = 2 To LastRow
        SavePDFAs Cells(i, 1).Value, Cells(i, 2).Value
    Next i
       'Adjust the two columns.
    Columns("A:B").EntireColumn.AutoFit
        'Inform the user that conversion finished.
    MsgBox "All files were converted successfully!", vbInformation, "Finished"
   End Sub Private Sub SavePDFAs(PDFPath As String, FileExtension As String)
       '---------------------------------------------------------------------------------------
    'Saves a PDF file as other format using Adobe Professional.
       'In order to use the macro you must enable the Acrobat library from VBA editor:
    'Go to Tools -> References -> Adobe Acrobat xx.0 Type Library, where xx depends
    'on your Acrobat Professional version (i.e. 9.0 or 10.0) you have installed to your PC.
       'Alternatively you can find it Tools -> References -> Browse and check for the path
    'C:Program FilesAdobeAcrobat xx.0Acrobatacrobat.tlb
    'where xx is your Acrobat version (i.e. 9.0 or 10.0 etc.).
       'By Christos Samaras
    'Date: 30/03/2013
    'https://myengineeringworld.net/////
    '---------------------------------------------------------------------------------------
   
    Dim objAcroApp      As Acrobat.AcroApp
    Dim objAcroAVDoc    As Acrobat.AcroAVDoc
    Dim objAcroPDDoc    As Acrobat.AcroPDDoc
    Dim objJSO          As Object
    Dim boResult        As Boolean
    Dim ExportFormat    As String
    Dim NewFilePath     As String
           'Initialize Acrobat by creating App object.
    Set objAcroApp = CreateObject("AcroExch.App")
       'Set AVDoc object.
    Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")
       'Open the PDF file.
    boResult = objAcroAVDoc.Open(PDFPath, "")
           'Set the PDDoc object.
    Set objAcroPDDoc = objAcroAVDoc.GetPDDoc
       'Set the JS Object - Java Script Object.
    Set objJSO = objAcroPDDoc.GetJSObject
       'Check the type of conversion.
    Select Case LCase(FileExtension)
        Case "eps": ExportFormat = "com.adobe.acrobat.eps"
        Case "html", "htm": ExportFormat = "com.adobe.acrobat.html"
        Case "jpeg", "jpg", "jpe": ExportFormat = "com.adobe.acrobat.jpeg"
        Case "jpf", "jpx", "jp2", "j2k", "j2c", "jpc": ExportFormat = "com.adobe.acrobat.jp2k"
        Case "docx": ExportFormat = "com.adobe.acrobat.docx"
        Case "doc": ExportFormat = "com.adobe.acrobat.doc"
        Case "png": ExportFormat = "com.adobe.acrobat.png"
        Case "ps": ExportFormat = "com.adobe.acrobat.ps"
        Case "rft": ExportFormat = "com.adobe.acrobat.rft"
        Case "xlsx": ExportFormat = "com.adobe.acrobat.xlsx"
        Case "xls": ExportFormat = "com.adobe.acrobat.spreadsheet"
        Case "txt": ExportFormat = "com.adobe.acrobat.accesstext"
        Case "tiff", "tif": ExportFormat = "com.adobe.acrobat.tiff"
        Case "xml": ExportFormat = "com.adobe.acrobat.xml-1-00"
        Case Else: ExportFormat = "Wrong Input"
    End Select
       'Check if the format is correct and there are no errors.
    If ExportFormat <> "Wrong Input" And Err.Number = 0 Then
               'Format is correct and no errors.
               'Set the path of the new file. Note that Adobe instead of xls uses xml files.
        'That's why here the xls extension changes to xml.
        If LCase(FileExtension) <> "xls" Then
            NewFilePath = WorksheetFunction.Substitute(PDFPath, ".pdf", "." & LCase(FileExtension))
        Else
            NewFilePath = WorksheetFunction.Substitute(PDFPath, ".pdf", ".xml")
        End If
               'Save PDF file to the new format.
        boResult = objJSO.SaveAs(NewFilePath, ExportFormat)
               'Close the PDF file without saving the changes.
        boResult = objAcroAVDoc.Close(True)
               'Close the Acrobat application.
        boResult = objAcroApp.Exit
           Else
               'Something went wrong, so close the PDF file and the application.
               'Close the PDF file without saving the changes.
        boResult = objAcroAVDoc.Close(True)
               'Close the Acrobat application.
        boResult = objAcroApp.Exit     End If
           'Release the objects.
    Set objAcroPDDoc = Nothing
    Set objAcroAVDoc = Nothing
    Set objAcroApp = Nothing
       
End Sub

Video

You can watch the VBA code in action on this short video:

Download it from here

Download.jpg

The new file can be opened with Excel 2007 or newer. Please, remember that you will need Adobe Professional for the conversion. The code will NOT work with Adobe Reader.

Read also

VBA Macro To Open A PDF File
Open PDF File With VBA

How to change a tab-delimited text file to a CSV file

You can convert the file to a CSV (comma-separated values) file using a spreadsheet application, such as Microsoft Excel or LibreOffice Calc. Using Microsoft Excel. Using LibreOffice Calc. Using Microsoft Excel. Open Microsoft Excel. Click the File tab in the Ribbon menu and click the Open option. Click the Browse option to bring up the Open or ...

Updated: 11/13/2018 by Computer Hope

Microsoft Excel logo

A tab-delimited text file is a text file whose units of text are separated by a tab character. These tab characters organize the text into tabular data. You can convert the file to a CSV (comma-separated values) file using a spreadsheet application, such as Microsoft Excel or LibreOffice Calc.

  • Using Microsoft Excel.
  • Using LibreOffice Calc.

Using Microsoft Excel

  1. Open Microsoft Excel.
  2. Click the File tab in the Ribbon menu and click the Open option.
  3. Click the Browse option to bring up the Open or Browse window.
  4. Next to the File Name field, click the File Type drop-down menu and select the Text Files option.
  5. Navigate to the location of the text file, select it, and click the Open button.
  6. In the Text Import Wizard, the Delimited option should already be selected (shown below). Click the Next button to go to Step 2 of the wizard.

Microsoft Excel Text Import Wizard Step 1

Note

If the text file has a header row (row that identifies the name of each column of data), click the check box for My data has headers.

  1. Make sure the Tab option is checked and validate the data is separated correctly in the Data preview section.

Microsoft Excel Text Import Wizard Step 2

  1. To format each column of data, like text, date, or numeric, click the Next button and select the format type for each column of data. If you don't need to format each column of data, click the Finish button.
  2. The text file data is converted to spreadsheet format. Again, click the File tab in the Ribbon menu and select the Save As option.
  3. In the Save As window, select the CSV (Comma delimited) (*.csv) option in the Save as type drop-down menu.
  4. Type a name for the CSV file in the File name field, navigate to where you want to save the file, then click the Save button.

The file is saved as a CSV file, which is a text file that uses commas as delimiters. It can be opened in a spreadsheet application or in any text editor.

Using LibreOffice Calc

LibreOffice is a free, open-source office productivity software suite that runs on many operating systems, including Linux, Microsoft Windows, and macOS. Its spreadsheet application is LibreOffice Calc.

Follow these steps to open a tab-delimited text file in LibreOffice Calc, view it as a spreadsheet, and save it as a CSV file.

  1. Open LibreOffice Calc.
  2. From the File menu, select Open and select your tab-delimited text file.

LibreOffice Calc file menu

  1. Once the Text Import dialog box opens, by default, it interprets tabs, commas, and semicolons as column delimiters, and newlines as row delimiters.

LibreOffice Calc Text Import options dialog

  1. Unselect Comma and Semicolon.

Unselecting the comma and semicolon delimiter options in the LibreOffice Calc Text Import options dialog

  1. Click OK. Your data loads into a spreadsheet.

Data loaded into spreadsheet

  1. You can now select Save As from the file menu, and choose Text CSV (.csv) as the File Type.

Saving as CSV

  1. To save the file, click Save.

Additional information

Convert Json to csv and csv to Json in Excel VBA

25-10-2017 · Usually, If the JSON file is small or has a simple structure then I would use any of the online converters to quickly convert it to CSV. But if the JSON is complex or needs more customizations then I would convert it using VBA.

25-10-2017

I recently received a query on how to convert JSON to CSV. Usually, If the JSON file is small or has a simple structure then I would use any of the online converters to quickly convert it to CSV. But if the JSON is complex or needs more customizations then I would convert it using VBA.

I’m using VBA-JSON library for parsing JSON data. Check out the previous article to quickly understand How to parse JSON in Excel VBA

Getting started

Let’s understand this in 3 steps.

  1. How to read a file in VBA
  2. How to write to CSV or Text file in VBA
  3. Combine above steps – Read JSON file > parse JSON > Write to CSV file

Read JSON file using VBA

JSON file can be read in 2 ways in VBA.

  1. Using FileSystemObject
  2. Using Open File for Input method

I’ve explained FileSystemObject in the Excel JSON article already so let’s use Open File for Input method.

I’ve saved a sample JSON as posts.json file which looks like image below. You can also download it from here

JSON looks like this –

[
  {
    "userId": 1,
    "id": 1,
    "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
    "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
  },
  {
    "userId": 1,
    "id": 2,
    "title": "qui est esse",
    "body": "est rerum tempore vitae\nsequi sint nihil reprehenderit dolor beatae ea dolores neque\nfugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis\nqui aperiam non debitis possimus qui neque nisi nulla"
  }
]

Have a look at the code once. Here’s the code to read a file in VBA –

Public Sub readfile()
Dim jsonData As String, jsonString As String
myfile = Application.ActiveWorkbook.Path & "\posts.json"
fileNum = FreeFile
Open myfile For Input As #fileNum
jsonString = Input(LOF(fileNum), fileNum) 'LOF Function returns the size of the file in bytes
MsgBox (jsonString)
Close #fileNum
End Sub

Code Explanation

  1. I saved my JSON file in the same folder as the excel file so I used Application.ActiveWorkbook.Path to get the current path. You can just set it to full file path if required.
  2. FreeFile method is used to get the next free file number which will be used as a temporary memory stack. In the above code, you can just use fileNum = 1 but it might cause some issues when you are working with multiple files simultaneously.
  3. Then open the file using Open For Input method, read the entire file and store it in jsonString

Write to text file in VBA

Writing to a file is pretty simple in VBA. Here’s the code

Sub writefile()
myfile = Application.ActiveWorkbook.Path & "\sample.txt"
fileNum = FreeFile
Open myfile For Output As #fileNum
Print #fileNum, "I am writing to a text file using VBA!"
Close #fileNum
End Sub

Code Explanation

It’s self-explanatory if you read the first Code explanation. Print method writes to a new line each time it is called.

Write to CSV file in VBA

Now Let’s understand how to write to a CSV file. CSV file is nothing but a text file with rows separated by line and columns separated by a comma.

We can directly export an excel file as CSV but I’m showing this example only to explain the CSV format and how to programmatically generate them so that this knowledge can be used in converting JSON to CSV

Let’s say I have some data as shown in Image below

sample csv data - JSON to CSV

Here’s the code to write data to a CSV file

Sub writeToCSVfile()
Dim rng As Range, columnsNum As Integer, cell As Variant, rowData As String
Set rng = Range(Sheets(1).Range("A1"), Sheets(1).Range("A10000").End(xlUp)) 'Dynamic range
columnsNum = 3
myfile = Application.ActiveWorkbook.Path & "\test.csv"
fileNum = FreeFile
Open myfile For Output As #fileNum
For Each cell In rng
rowData = ""
    For i = 0 To (columnsNum - 1)
        If i = (columnsNum - 1) Then 'Check if last column
        rowData = rowData   Chr(34)   Trim(cell.Offset(0, i).Value)   Chr(34)
        Else
        rowData = rowData   Chr(34)   Trim(cell.Offset(0, i).Value)   Chr(34)   ","
        End If
    Next
Print #fileNum, rowData
Next
Close #fileNum
MsgBox ("complete")
End Sub

Code Explanation

  1. I have set the range to be dynamic so that same code works for 3 rows and also 1000 rows. Range("A10000").End(xlUp) method gets the last used cell in column A. In this case, Range becomes A1:A3
  2. ColumnsNum can also be made dynamic but usually, Columns are not very high in number so I mentioned the number of columns manually here. Change the columnsNum according to your data.
  3. Then we open a file, Loop through each row, Loop through each column in the row and concatenate the columns to one row separated by a comma and print the row data and close the file after the loop is complete. CHR(34) = Double Quote
  4. CSV File will be saved in the same as folder as the excel file in which this code is executed.

Convert JSON to CSV

Now You know how to read a file and write a file. Let’s read a JSON file, parse it and convert it to CSV file. I’m using a library VBA-JSON to parse JSON in VBA. I’ve explained it in detail in Import JSON to Excel post. If you haven’t read that then go have a look before you read this.

Here’s the sample JSON

Sample JSON looks like this –

[
  {
    "userId": 1,
    "id": 1,
    "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
    "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
  },
  {
    "userId": 1,
    "id": 2,
    "title": "qui est esse",
    "body": "est rerum tempore vitae\nsequi sint nihil reprehenderit dolor beatae ea dolores neque\nfugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis\nqui aperiam non debitis possimus qui neque nisi nulla"
  }
]

Code to convert above JSON to CSV

Sub jsonToCSV()
Dim jsonData As String, JSON As Object, jsonString As String, rowData As String

jsonFile = Application.ActiveWorkbook.Path & "\posts.json" 'Input json file
jsonfileNum = FreeFile
Open jsonFile For Input As #jsonfileNum
jsonString = Input(LOF(jsonfileNum), jsonfileNum)
Set JSON = ParseJson(jsonString)
Close #jsonfileNum

csvFile = Application.ActiveWorkbook.Path & "\posts.csv" 'Output csv file
csvFileNum = FreeFile
Open csvFile For Output As #csvFileNum
Print #csvFileNum, "userId,Id,title,body" 'Print Headers

rowData = ""
For Each item In JSON
    userIdField = formatField(item("userId"))
    idField = formatField(item("id"))
    titleField = formatField(item("title"))
    bodyField = formatField(item("body"), False) 'Not adding comma to last field
    rowData = userIdField   idField   titleField   bodyField
    Print #csvFileNum, rowData
Next
Close #csvFileNum
End Sub



Function formatField(val, Optional addComma As Boolean = True)
If (addComma = False) Then
formatField = Chr(34)   Trim(val)   Chr(34)
Else
formatField = Chr(34)   Trim(val)   Chr(34)   ","
End If
End Function

Code Explanation

  1. Firt we open the JSON file, read the data, parse it and store it to JSON variable.
  2. Print headers manually. Remove this line if not required.
  3. Loop through each object in JSON and get appropriate fields. Each filed has to be wrapped within quote and comma has to be added for all fields except for last field.
  4. formatField function wraps the fields with quotes and comma
  5. Finally print each rowData and close the file!

Note that this code works only for above JSON structure. You have to modofy the code to match your JSON structure. Read JSON tutorial here and understadn the structure.

Convert CSV to JSON in VBA

Let’s convert the above generated CSV to JSON. Here’s the code.

Public Sub csvToJsonfile()
Dim rng As Range, items As New Collection, myitem As New Dictionary
csvfileName = "posts.csv"
csvfilePath = Application.ActiveWorkbook.Path
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & csvfilePath & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
strSQL = "SELECT * FROM " & csvfileName
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst
Do
    myitem("userId") = rs("userId")
    myitem("id") = rs("id")
    myitem("title") = rs("title")
    myitem("body") = rs("body")
    items.Add myitem
    Set myitem = Nothing
    rs.MoveNext
Loop Until rs.EOF
jsonFile = Application.ActiveWorkbook.Path & "\generated-posts.json"
jsonfileNum = FreeFile
Open jsonFile For Output As #jsonfileNum
Print #jsonfileNum, ConvertToJson(items, Whitespace:=2)
Close #jsonfileNum
rs.Close
MsgBox ("complete")
End Sub

Code explanation

I’m using ADODB recordset to read CSV file. We can also use Open as Input method and read line by line getting each row. But csv doesn’t always mean that new line = new row. Sometimes there may be line breaks in the fields. So I’m using ADODB connection.

  1. First open CSV file using ADODB connection. Read ADODB.Open method’s documentation here – Open Method (ADO Recordset)
  2. Loop through each row and add the fields to a dictionary and push that dictionary into items collection and convert it to JSON using ConvertToJson method
  3. Finally write the JSON to a new file using Open For Output method

Wrapping up

Read JSON tutorial for beginners, understand JSON and adapt the code for your requirement!

If you have and questions or feedback, comment below.

sourceforge.net

This is an Excel based VBA script used to import bulk .VCF files that contain more than 1 Vcard and then convert them to a comma separated .CSV file or Excel xls file that can then be imported into Outlook, Google, or any other application that supports import of contacts using CSV files. This has been written to support VCF 2.0, 2.1, 3.0 and 4.0 formatted files including …

  • Fortinet can help you achieve agile and robust protection for your AWS and multi-cloud environment by leveraging best practices within your chosen framework. Cloud Security Posture Assessment Service – FortiGate-VM JumpStart Consulting Service – FortiWeb-VM JumpStart Consulting Service
  • CSS offers a full stack of financial management tools and technology platforms for originations, credit granting, revenue cycle management, billing, collections, legal and portfolio management that work as independent systems or collectively as a turn-key enterprise financial ecosystem.
  • 1
  • 2
  • 3
  • 4
  • CakeHR is an award-winning, SaaS-based HR system that relies on the self-service model to simplify HR. Coupling intuitive design and ease of navigation with a robust set of required features, CakeHR is the perfect mix of accessibility and functionality for SMEs. With CakeHR small and medium businesses can enjoy 360 feedback, goal tracking, expense overviews, employee scheduling, recruiting and strong, visual reporting – from one dashboard.
  • 5
  • 6
  • 7
  • 8
  • 9
  • Engine Yard is a data-driven, NoOps, PaaS solution for deploying and managing applications on AWS.
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
powerusers.microsoft.com

A HTML file is essentially already a text file... if you're saving the file somewhere just change the extension to TXT. To go from HTML to CSV... you need to extract data from the HTML file and the format the data... this could be complex depending on what you are trying to extract and format. HTH. Jay

codeproject.com

Accept Solution Reject Solution. easy Solution: Just replace the extension to .txt. Rename or copy MyFile.csv to MyFile.txt and you are done. Or do nothing and use the csv file as a text file directly. Nota: a csv file is a text file! Permalink. Posted 26-Sep-16 15:00pm. Patrice T.

devhut.net

This is somewhat a continuation on my previous post VBA – Convert XLS to XLSX in which I provided a simple little procedure to upgrade an older xls file to the newer xlsx file format.. I thought to myself, would it be nice to have a more versatile function that could migrate between various other common file formats.

Excel VBA convert CSV to Excel

29-01-2017 · Convert CSV to Excel in Excel VBA. CSV is a commonly used format when you need to interface data from one system to another system. For modification purpose, it is best to convert to Excel format because it can contain formatting. This tutorial demonstrates how to write a Macro to convert all CSV files in a folder to Excel. VBA Code – Convert ...

29-01-2017

This Excel tutorial explains how to convert CSV to Excel in Excel VBA.

You may also want to read:

Access Excel FSO File Methods

Import Chinese CSV to Excel

Excel VBA Import CSV into Excel using Workbooks.OpenText Method

Convert CSV to Excel in Excel VBA

CSV is a commonly used format when you need to interface data from one system to another system. For modification purpose, it is best to convert to Excel format because it can contain formatting. This tutorial demonstrates how to write a Macro to convert all CSV files in a folder to Excel.

VBA Code – Convert CSV to Excel

You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT F11) > Tools > References > Check the box Microsoft Scripting Runtine

loop_through_workbook

Create a new workbook and copy the file to a new Module, change the desired folder path highlighted in blue.

Basically what the Macro does is to open csv in Excel and then save as xlsx (xlOpenXMLWorkbook) under specified folder.

Public Sub csvToxls()
    Dim FSO As Object
    Dim folder As Object
    Dim wb As Object
    
    csvPath = "C:\test\"
    xlsPath = "C:\test\xlsFolder"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set csvFolder = FSO.GetFolder(csvPath)
    
    If FSO.FolderExists(xlsPath) = False Then
        FSO.createFolder (xlsPath)
    End If
    
    Set xlsFolder = FSO.GetFolder(xlsPath)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
        
    For Each wb In csvFolder.Files
        If LCase(Right(wb.Name, 3)) = "csv" Then
            Set activeWB = Workbooks.Open(wb)
            activeWB.SaveAs Filename:=xlsPath & "\" & Left(activeWB.Name, Len(activeWB.Name) - 3) & "xlsx", FileFormat:=xlOpenXMLWorkbook
            activeWB.Close True
        End If
    Next
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Example – Convert CSV to Excel

Suppose we have two csv files under  C:\test\

Create a workbook and copy the Macro, then run the Macro

A new folder called xlsFolder is created under C:\test\   , and two workbooks are generated in xlsx format.

VBA to Convert Excel File to CSV File and CSV to Excel ...

We can convert and save Excel file to CSV and CSV file to Excel using VBA code or Macro. This example explains simple VBA Code to Open Excel file and then sa...

forum.ozgrid.com

Re: Convert .xls To .txt Without Losing Decimal Accuracy. I have a tool for saving ranges (or worksheets) to CSV files. Among other things, it truly does save as CSV as opposed to the Excel method of saving as CSFV (comma seperated formatted values).. One really aggravating thing about Excel and the CSV feature has to do with the general format that will …

convertio.co

Convert your csv files to txt online & free. Document Converter. Choose Files. Choose Files. Drop files here. 100 MB maximum file size or Sign Up. Image. Document. EBook. Audio.

social.msdn.microsoft.com

Sub convertToWord() Dim MyObj As Object, MySource As Object, file As Variant file = Dir("D:\OfficeDev\Word1505\Pdf\" & "*.pdf") 'pdf path Do While (file <> "") ChangeFileOpenDirectory "D:\OfficeDev\Word1505\Pdf\" Documents.Open FileName:=file, ConfirmConversions:=False, ReadOnly:= _ False, AddToRecentFiles:=False, …

  • Article
  • 2 minutes to read

This site contains the documentation for Office 365 REST APIs exposed on the OneNote and Outlook endpoints. The documentation is no longer actively updated. It is here for your reference, in case you have already included any of the API in your project.

Office 365 services, such as OneNote, Outlook, Excel, OneDrive, Microsoft Teams, Planner, and SharePoint, are now exposed in Microsoft Graph. Microsoft Graph is a unified API endpoint for accessing data across Microsoft 365, which includes Office 365, Enterprise Mobility, and Security and Windows services. It provides a simplified developer experience, with one endpoint and a single authentication token that gives your app access to data across all these services.

Our recommendation:

  • If you are starting a new app, please use Microsoft Graph.
  • If you are maintaining an existing project, use this documentation set for reference. In addition, plan your move to the latest APIs in Microsoft Graph to take advantage of all the new functionality and innovation there.
  • If you are adding new functionality to an existing project, integrate that new functionality by using Microsoft Graph.

With Microsoft Graph, you can access Office 365 data and more, from solutions across mobile, web, and desktop platforms. So whether you're building web applications by using .NET, PHP, Java, Python, or Ruby on Rails, or creating apps for the Universal Windows Platform (UWP), iOS, Android, or on another device platform, it's your choice.

In this section

  • Article
  • 2 minutes to read

This site contains the documentation for Office 365 REST APIs exposed on the OneNote and Outlook endpoints. The documentation is no longer actively updated. It is here for your reference, in case you have already included any of the API in your project.

Office 365 services, such as OneNote, Outlook, Excel, OneDrive, Microsoft Teams, Planner, and SharePoint, are now exposed in Microsoft Graph. Microsoft Graph is a unified API endpoint for accessing data across Microsoft 365, which includes Office 365, Enterprise Mobility, and Security and Windows services. It provides a simplified developer experience, with one endpoint and a single authentication token that gives your app access to data across all these services.

Our recommendation:

  • If you are starting a new app, please use Microsoft Graph.
  • If you are maintaining an existing project, use this documentation set for reference. In addition, plan your move to the latest APIs in Microsoft Graph to take advantage of all the new functionality and innovation there.
  • If you are adding new functionality to an existing project, integrate that new functionality by using Microsoft Graph.

With Microsoft Graph, you can access Office 365 data and more, from solutions across mobile, web, and desktop platforms. So whether you're building web applications by using .NET, PHP, Java, Python, or Ruby on Rails, or creating apps for the Universal Windows Platform (UWP), iOS, Android, or on another device platform, it's your choice.

In this section