CSV creation from TextIo not creating true csv.

(0) Share

Report

Posted on by b_radlyjames

3,155

I am trying to use TextIo to create a CSV. It seems simple enough, but my .csv isn't showing items separated into columns when opening in Excel. Code:

#File

TextIo csvFile;

new FileIOPermission("C:\Temp\csvFile.csv", #io_write).assert();

csvFile = new TextIo("C:\Temp\csvFile.csv", #io_write);

csvFile.outFieldDelimeter(',');

If (!csvFile ... ... Throw error if issue with file exists

while select custTable

{

csvFile.write(custTable.AccountNum, custTable.Name());

csvFile.writeExp([custTable.AccountNum, custTable.name()]);

}

//Revert Assert

Neither of those ('write' or 'writeExp') seem to be doing it right. They end up in one column in excel with a comma between the values.

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (1)

Suggested answer

Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

You didn't explain what's wrong with your file. CSV is a text format, so you should open your file in a text editor and check it there. If it's all right, the problem must be with Excel. If it isn't all right, you'll know what to fix.

Nevertheless switch to CommaTextIo before starting any time-consuming investigation.

Was this reply helpful? Yes No
Suggested answer

b_radlyjames 3,155 on at

Like (0)

Report

It is Excel with the problem.

CommaTextIo is bad solution. It puts "" around everything. In some cases that might be okay, but I'm using this to import into another program and the "" are not acceptable.

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

That's completely valid CSV according to RFC 4180. If your other program isn't able to accept valid CSV, you'll have to look for another format.

Nevertheless if you don't want CommaTextIo, return to the first part of my previous reply.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi

I am not sure if you really have to use TextIO for some reason. However, i can achieve the required result from the following lines of code. Here you will be having customer number and customer name in separate columns.

Please check and see if resolved your issue

CommaIO commaIO;

FileName fileName;

CustTable custTable;

fileName = "C:\\Customer sheet.csv";

commaIO = new CommaIO(fileName,'W');

while select custTable

{

commaIO.write(custTable.AccountNum, custTable.name());

}

WINAPI::createFile(fileName);

Happy to hear if it resolved your issue.

Regards

Syed

Was this reply helpful? Yes No
Suggested answer

Faisal Fareed 10,796 User Group Leader on at

Like (0)

Report

Hi,

Syed's suggested code should work without any error. You can also have a look on below code I added codePage parameter into CommanTextIo() to make it compatible with excel in case it does not work.

CommaTextIo txIoWrite;

FileIOPermission fioPermission;

CustTable custTable;

str filePath;

str sFileName;

sFileName = "CustomerSheet" + ".csv";

filePath = "FilePath";

// Assert permission.

fioPermission = new FileIOPermission (@filePath ,"RW");

fioPermission.assert();

// If the test file already exists, delete it.

if (System.IO.File::Exists(@filePath))

{

System.IO.File::Delete(@filePath);

}

// Open a test file for writing.

// "W" mode overwrites existing content, or creates the file.

txIoWrite = new CommaTextIo(@filePath ,"W", 65001); // Must be UTF8 to work with Excel.

while select custTable

{

txIoWrite.write(custTable.AccountNum, custTable.name());

}

// Close the test file.

txIoWrite = null;

Was this reply helpful? Yes No
Verified answer

Mohammad Raziq Ali 2,486 on at

Like (0)

Report

Hi Faisal,

Your code works well as it has created a file with .csv extension in specified directory and it is flexible with different IO modes as well. If I open that file with notepad text editor, it fulfills all the criteria i.e., values are separated with comma but when I open same file with excel, values are merged in same cell which I am expecting those values in different cells with out comma. Any Idea???

Thanks,

Raziq

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Yes there are different ways of exporting into CSV.

The reason i didn't use commaTextIo is because James said

"CommaTextIo is bad solution. It puts "" around everything. In some cases that might be okay, but I'm using this to import into another program and the "" are not acceptable."

I have tested the code I mentioned with commaIO that it is displaying correct values

- files are saved in .csv extension

- values are in different columns

- didn't put "" around everything.

Please let me know if this is what you are looking for.

Was this reply helpful? Yes No
Suggested answer

Rudi Hansen 4,075 on at

Like (0)

Report

Are .csv files not supposed to be ; separated?

In the case i have where i need to make an csv file from ax i use CommaIO and then outFieldDelimiter(';');

So as i see it your main problem is that you are using , as separator not ;

Was this reply helpful? Yes No
Suggested answer

Faisal Fareed 10,796 User Group Leader on at

Like (1)

Report

Hi Raziq,

I tested this code and have used the same code in many places to export data into .csv file and all files are opening in excel in seperate cells. As I highligted in code that line of code will make it excel compatible and you don't even need to go through those wizard steps to open .csv files into excel file. For example; don't need to provide any delimeter (; or tab) to seperate data in cells.

You can share code with me and I will have a look on it where it goes different from expected behaviour.

Was this reply helpful? Yes No
Mohammad Raziq Ali 2,486 on at

Like (0)

Report

Hi Faisal,

Yes, I have tested it your code, it working fine in text editor and Excel as well.

If the file is existed already, it is opt for deleting through code but it prompted with unknown results then I realized that, I don't have administrative privilege to delete the file.

It is security concern but any how your code works well.

Thanks,

Raziq

Was this reply helpful? Yes No