pesky commas

[dandelion]

Hi

I've been working on a hierarchical import file creator using macros in a spreadsheet to transpose values from a flat file - and I'm nearly there (I hope).

My current problem is that the .csv I am generating has trailing commas on each line, and at the moment I can't see how to get rid of them. I had hoped Actinic might ignore them, but I think they are causing the following error on import:

no fields found in first line of file

Apart from these trailing commas, my import-ready file looks similar to my exported reference file, with one further exception:

I have yet to hard code in the fixed values for the bulk of the section, product and custom property lines (those values which are constant and don't appear in the flat file).

Does anybody know which if either of these issues might be causing the error?

Thanks, Dan

[Jan]

Are you sure that you are importing it as a hierarchical file in Actinic because that seems like a flat file error.

What happens when you delete the commas from the end of the first few lines?

If you post it (or a sample of it) here it would be easier to help out with the error.

Regards,

[dandelion]

Hi Jan

Yes, it's definitely a hierarchical. I have taken out the trailing commas in the header area, and for the first few products, and I am now past the 'no fields...' error, and into a wrong number of fields type error, so I am confident that if I can find some smart way of deleting the unwanted commas from the original .csv export (I don't think it's possible to repress them in OpenOffice), and also hardcoding in the constants for section, product and cp lines, I should be there. (touch melamine!)

Thanks for your advice

Dan

[cdicken]

I've been working with hierarchical files all day today (updating the shipping tables) and I have found the best way to get rid of commas is...

...do some clever Finds and Replaces in Word.

For example, I had about 1000 records - some of which had a comma at the end, some of whom did not, What I did to make them all the same was:

Find all ^p and replace with ,^p

Then

Find all ,, and replace with ,

This gave one comma at the end of every row. I then just copied and pasted the edited code into Notepad.

[dandelion]

Cheers Chris

The trouble is I've got ,,,,,,,,, on some rows and,,, on others and ,,,,,,,,,,,,,,,,, - you get the picture. So I've been trying to write a macro in OpenOfficeBasic, which I'd never used until yesterday, to find carriage returns, and then delete any preceding commas. It shouldn't be too hard, but it's taking a while. I've already managed to write one to do all the transposing and end,section lines etc, after all.

However, I suppose the quick solution is to do as you said, cos the header area aside, each row will have one of 4 numbers of commas at the end (section, product, custom prop, or end lines), so I can do find and replace for those 4 numbers of commas.

But now it's the weekend!

Thanks for the reply, if I don't nail the macro on Monday morning, I may take the find and replace route.

Dan