I have a problem and I hope someone can help
I export 3 fields to a CSV file Code C(15), price N(10,2) , supcode C(30)
The code and the supcode may be pure numbers Example supcode may be 12345.56000
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
I only dabble with Excel but I suspect that you can get your expected results by setting it up to use five decimal places instead of two. I thought you could convert it in Foxpro with str(val( '12345.56'), 10, 5) but for some reason that only gives 4 decimal places even with decimals set to 6. I guess that leaves string manipulation where you find the location of the dot from the right edge of the trimed string with the RAT function and add as many zero as you need.
I'm curious why the extra zeros are important.
Joe
On Sun, Feb 28, 2016 at 6:36 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
I have a problem and I hope someone can help
I export 3 fields to a CSV file Code C(15), price N(10,2) , supcode C(30)
The code and the supcode may be pure numbers Example supcode may be 12345.56000
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
The Supplier code could be letters and number, just letters, or just numbers.
The supplier code may be 12345.1234567890 It's nothing to do with decimal point. It's a character. If the code is ABC12345.1234567890 all is fine.
The import routine is append from file IMPFILE type csv
On Mon, Feb 29, 2016 at 1:15 PM, Joe Yoder joe@wheypower.com wrote:
I only dabble with Excel but I suspect that you can get your expected results by setting it up to use five decimal places instead of two. I thought you could convert it in Foxpro with str(val( '12345.56'), 10, 5) but for some reason that only gives 4 decimal places even with decimals set to 6. I guess that leaves string manipulation where you find the location of the dot from the right edge of the trimed string with the RAT function and add as many zero as you need.
I'm curious why the extra zeros are important.
Joe
On Sun, Feb 28, 2016 at 6:36 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
I have a problem and I hope someone can help
I export 3 fields to a CSV file Code C(15), price N(10,2) , supcode C(30)
The code and the supcode may be pure numbers Example supcode may be 12345.56000
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Two points: 1) Str(val( '12345.56'), 10, 5) only gives 4 decimal places because you lose a place to the decimal point. Using str(val( '12345.56'), 11, 5) instead will work in the first example, but not in Sytze's second one. 2) Neither CSV no Excel support strict types. Either will always interpret field content with only numbers, a single decimal point, or a leading hyphen as numeric. The only way around this is to export an additional character such as a tilde (~), pipe (|), or other characters that don't ever appear in your data, then strip it out when importing back to FoxPro.
Mike
~~~~~~~~~~~~~~~~~~~~~~
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Sytze de Boer Sent: Sunday, February 28, 2016 5:20 PM To: profoxtech@leafe.com Subject: Re: Import/Export with CSV
The Supplier code could be letters and number, just letters, or just numbers.
The supplier code may be 12345.1234567890 It's nothing to do with decimal point. It's a character. If the code is ABC12345.1234567890 all is fine.
The import routine is append from file IMPFILE type csv
On Mon, Feb 29, 2016 at 1:15 PM, Joe Yoder joe@wheypower.com wrote:
I only dabble with Excel but I suspect that you can get your expected results by setting it up to use five decimal places instead of two. I thought you could convert it in Foxpro with str(val( '12345.56'), 10, 5) but for some reason that only gives 4 decimal places even with decimals set to 6. I guess that leaves string manipulation where you find the location of the dot from the right edge of the trimed string with the RAT function and add as many zero as you need.
I'm curious why the extra zeros are important.
Joe
On Sun, Feb 28, 2016 at 6:36 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
I have a problem and I hope someone can help
I export 3 fields to a CSV file Code C(15), price N(10,2) , supcode C(30)
The code and the supcode may be pure numbers Example supcode may be 12345.56000
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Seems like excel is treating what looks like a number as number and discarding insignificant digits.
Have you tried importing the CSV and specifying the data type for each column ? e.g Text import Wizard.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Sytze de Boer Sent: Monday, 29 February 2016 11:20 AM To: profoxtech@leafe.com Subject: Re: Import/Export with CSV
The Supplier code could be letters and number, just letters, or just numbers.
The supplier code may be 12345.1234567890 It's nothing to do with decimal point. It's a character. If the code is ABC12345.1234567890 all is fine.
The import routine is append from file IMPFILE type csv
On Mon, Feb 29, 2016 at 1:15 PM, Joe Yoder joe@wheypower.com wrote:
I only dabble with Excel but I suspect that you can get your expected results by setting it up to use five decimal places instead of two. I thought you could convert it in Foxpro with str(val( '12345.56'), 10, 5) but for some reason that only gives 4 decimal places even with decimals set to 6. I guess that leaves string manipulation where you find the location of the dot from the right edge of the trimed string with the RAT function and add as many zero as you need.
I'm curious why the extra zeros are important.
Joe
On Sun, Feb 28, 2016 at 6:36 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
I have a problem and I hope someone can help
I export 3 fields to a CSV file Code C(15), price N(10,2) , supcode C(30)
The code and the supcode may be pure numbers Example supcode may be 12345.56000
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Depending on what you are doing with the data you could consider editing the CSV with notepad. That would avoid Excels assumptions. - Joe
On Sun, Feb 28, 2016 at 7:30 PM, Darren foxdev@ozemail.com.au wrote:
Seems like excel is treating what looks like a number as number and discarding insignificant digits.
Have you tried importing the CSV and specifying the data type for each column ? e.g Text import Wizard.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Sytze de Boer Sent: Monday, 29 February 2016 11:20 AM To: profoxtech@leafe.com Subject: Re: Import/Export with CSV
The Supplier code could be letters and number, just letters, or just numbers.
The supplier code may be 12345.1234567890 It's nothing to do with decimal point. It's a character. If the code is ABC12345.1234567890 all is fine.
The import routine is append from file IMPFILE type csv
On Mon, Feb 29, 2016 at 1:15 PM, Joe Yoder joe@wheypower.com wrote:
I only dabble with Excel but I suspect that you can get your expected results by setting it up to use five decimal places instead of two. I thought you could convert it in Foxpro with str(val( '12345.56'), 10, 5) but for some reason that only gives 4 decimal places even with decimals set to 6. I guess that leaves string manipulation where you find the location of the dot from the right edge of the trimed string with the RAT function and add as many zero as you need.
I'm curious why the extra zeros are important.
Joe
On Sun, Feb 28, 2016 at 6:36 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
I have a problem and I hope someone can help
I export 3 fields to a CSV file Code C(15), price N(10,2) , supcode C(30)
The code and the supcode may be pure numbers Example supcode may be 12345.56000
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Sometimes I have to export data with fields that are all digits but are really character fields like PatientID, ClaimNumber, or similar where leadings zeros are necessary. I often prefix the field with an underscore to avoid have Excel screw me over.
hth, --Mike
On 2016-02-28 19:19, Sytze de Boer wrote:
The Supplier code could be letters and number, just letters, or just numbers.
The supplier code may be 12345.1234567890 It's nothing to do with decimal point. It's a character. If the code is ABC12345.1234567890 all is fine.
The import routine is append from file IMPFILE type csv
On Mon, Feb 29, 2016 at 1:15 PM, Joe Yoder joe@wheypower.com wrote:
I only dabble with Excel but I suspect that you can get your expected results by setting it up to use five decimal places instead of two. I thought you could convert it in Foxpro with str(val( '12345.56'), 10, 5) but for some reason that only gives 4 decimal places even with decimals set to 6. I guess that leaves string manipulation where you find the location of the dot from the right edge of the trimed string with the RAT function and add as many zero as you need.
I'm curious why the extra zeros are important.
Joe
On Sun, Feb 28, 2016 at 6:36 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
I have a problem and I hope someone can help
I export 3 fields to a CSV file Code C(15), price N(10,2) , supcode C(30)
The code and the supcode may be pure numbers Example supcode may be 12345.56000
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
On Sun, 28 Feb 2016, at 11:36 PM, Sytze de Boer wrote:
Whats the best way to overcome this?
Don't use Excel as a CSV editor. CSV is a text format, you need a text editor or something that is designed to work with CSV properly. I use CSVEd (http://csved.sjfrancke.nl/) which is a dedicated CSV editor. I believe Notepad++ also has plugins that handle CSV.
Sytze, Do the export to XML and then you won't have the conversion problems.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Sytze de Boer Sent: 28 February 2016 23:36 To: profox profox@leafe.com Subject: Import/Export with CSV
I have a problem and I hope someone can help
I export 3 fields to a CSV file Code C(15), price N(10,2) , supcode C(30)
The code and the supcode may be pure numbers Example supcode may be 12345.56000
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
On Sun, Feb 28, 2016 at 6:36 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
If you're exporting a field that looks like a number, Excel will helpfully try to convert the column into a number. If you want it to remain as a text field, precede the fields with a single quote:
select [']+code, price, [']+supcode from mytable to tempcursor copy to tesfile.csv type csv
Alternatively, there may be some option in the Excel import dialog to turn off "Smart Parsing" or whatever Excel calls it. (LibreOffice user, sorry.)
I would use VFP to edit your csv table in orde to avoid Excel messing up your character fields into numberic Regards Koen
Op maandag 29 februari 2016 heeft Ted Roche tedroche@gmail.com het volgende geschreven:
On Sun, Feb 28, 2016 at 6:36 PM, Sytze de Boer <sytze.kiss@gmail.com javascript:;> wrote:
I make some changes to the price (using Excel) I now import from this same file and the supcode has changed to 12345.56
Whats the best way to overcome this?
If you're exporting a field that looks like a number, Excel will helpfully try to convert the column into a number. If you want it to remain as a text field, precede the fields with a single quote:
select [']+code, price, [']+supcode from mytable to tempcursor copy to tesfile.csv type csv
Alternatively, there may be some option in the Excel import dialog to turn off "Smart Parsing" or whatever Excel calls it. (LibreOffice user, sorry.)
[excessive quoting removed by server]
On Mon, Feb 29, 2016 at 6:47 AM, Koen Piller koen.piller@gmail.com wrote:
I would use VFP to edit your csv table in orde to avoid Excel messing up your character fields into numberic
Well, sure, but Sytze is using Excel (or more likely, having a client use Excel) for some reason he hasn't shared with us. A LOT of my clients do everything in Excel and expect us to magically move it in and out of VFP, so it's a handy skill to have.
I mean, if Sytze is editing his database in CSV, we ought to point out he should edit it in VFP. Browses are even better than CSV. :)
Suppose you are correct however in the question Sytzke said <I use> Regards Koen
Op maandag 29 februari 2016 heeft Ted Roche tedroche@gmail.com het volgende geschreven:
On Mon, Feb 29, 2016 at 6:47 AM, Koen Piller <koen.piller@gmail.com javascript:;> wrote:
I would use VFP to edit your csv table in orde to avoid Excel messing up your character fields into numberic
Well, sure, but Sytze is using Excel (or more likely, having a client use Excel) for some reason he hasn't shared with us. A LOT of my clients do everything in Excel and expect us to magically move it in and out of VFP, so it's a handy skill to have.
I mean, if Sytze is editing his database in CSV, we ought to point out he should edit it in VFP. Browses are even better than CSV. :)
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
[excessive quoting removed by server]
Don't know if this helps, but you could try changing the number format of columns 1 and 3 in the worksheet to "Text". From VFP, you'd do something like this:
*!* Assume oSheet is a reference to the Excel.ActiveSheet WITH oSheet .Columns(1).Numberformat = "@" .Columns(3).Numberformat = "@" ENDWITH
Laurie
On 29 February 2016 at 12:19, Koen Piller koen.piller@gmail.com wrote:
Suppose you are correct however in the question Sytzke said <I use> Regards Koen
Op maandag 29 februari 2016 heeft Ted Roche tedroche@gmail.com het volgende geschreven:
On Mon, Feb 29, 2016 at 6:47 AM, Koen Piller <koen.piller@gmail.com javascript:;> wrote:
I would use VFP to edit your csv table in orde to avoid Excel messing
up
your character fields into numberic
Well, sure, but Sytze is using Excel (or more likely, having a client use Excel) for some reason he hasn't shared with us. A LOT of my clients do everything in Excel and expect us to magically move it in and out of VFP, so it's a handy skill to have.
I mean, if Sytze is editing his database in CSV, we ought to point out he should edit it in VFP. Browses are even better than CSV. :)
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
[excessive quoting removed by server]
On Mon, Feb 29, 2016 at 6:30 AM, Ted Roche tedroche@gmail.com wrote:
Alternatively, there may be some option in the Excel import dialog to turn off "Smart Parsing" or whatever Excel calls it. (LibreOffice user, sorry.)
See Also: http://stackoverflow.com/questions/35118402/how-to-correctly-format-an-alpha...
Took a look at that link and it references an Excel add-on - "Power Query" - looks like a great additional to Excel. As a bonus seems to sort out the issue raised.
http://stackoverflow.com/questions/35118402/how-to-correctly-format-an-alpha -numerical-in-an-exported-csv-file-opened-in-exc?rq=1
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Ted Roche Sent: Tuesday, 1 March 2016 2:07 AM To: profoxtech@leafe.com Subject: Re: Import/Export with CSV
On Mon, Feb 29, 2016 at 6:30 AM, Ted Roche tedroche@gmail.com wrote:
Alternatively, there may be some option in the Excel import dialog to turn off "Smart Parsing" or whatever Excel calls it. (LibreOffice user, sorry.)
See Also: http://stackoverflow.com/questions/35118402/how-to-correctly-format-an-alpha -numerical-in-an-exported-csv-file-opened-in-exc?rq=1
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
[excessive quoting removed by server]
Darren, Good spotting!
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Darren Sent: 29 February 2016 21:22 To: profox@leafe.com Subject: RE: Import/Export with CSV
Took a look at that link and it references an Excel add-on - "Power Query" - looks like a great additional to Excel. As a bonus seems to sort out the issue raised.
http://stackoverflow.com/questions/35118402/how-to-correctly-format-an-alpha -numerical-in-an-exported-csv-file-opened-in-exc?rq=1
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Ted Roche Sent: Tuesday, 1 March 2016 2:07 AM To: profoxtech@leafe.com Subject: Re: Import/Export with CSV
On Mon, Feb 29, 2016 at 6:30 AM, Ted Roche tedroche@gmail.com wrote:
Alternatively, there may be some option in the Excel import dialog to turn off "Smart Parsing" or whatever Excel calls it. (LibreOffice user, sorry.)
See Also: http://stackoverflow.com/questions/35118402/how-to-correctly-format-an-alpha -numerical-in-an-exported-csv-file-opened-in-exc?rq=1
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
[excessive quoting removed by server]
Power Query is an add in for Excel 2010 and 2013. We are going to implement it in our newer BI model later this year.
Is it useful for VFP data? I don't think so.
We are consider it for repeat queries that could mimic a union clause in a query. Our ERP had separate tables for Order Headers depending on the business company data you are looking for. We have 5 different companies operating within our system depending on the type of business and the location it is.
All of that considered I don't think that Power Query will be any assistance to making a CSV output.
On Tue, Mar 1, 2016 at 2:24 AM, Dave Crozier DaveC@flexipol.co.uk wrote:
Darren, Good spotting!
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Darren Sent: 29 February 2016 21:22 To: profox@leafe.com Subject: RE: Import/Export with CSV
Took a look at that link and it references an Excel add-on - "Power Query"
- looks like a great additional to Excel. As a bonus seems to sort out the
issue raised.
http://stackoverflow.com/questions/35118402/how-to-correctly-format-an-alpha -numerical-in-an-exported-csv-file-opened-in-exc?rq=1
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Ted Roche Sent: Tuesday, 1 March 2016 2:07 AM To: profoxtech@leafe.com Subject: Re: Import/Export with CSV
On Mon, Feb 29, 2016 at 6:30 AM, Ted Roche tedroche@gmail.com wrote:
Alternatively, there may be some option in the Excel import dialog to turn off "Smart Parsing" or whatever Excel calls it. (LibreOffice user, sorry.)
See Also:
http://stackoverflow.com/questions/35118402/how-to-correctly-format-an-alpha -numerical-in-an-exported-csv-file-opened-in-exc?rq=1
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
[excessive quoting removed by server]
-
Alan Bourke -
Darren -
Dave Crozier -
Joe Yoder -
Koen Piller -
Laurie Alvey -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Michael Glassman -
Stephen Russell -
Sytze de Boer -
Ted Roche