cf_csv_to_file csv_query

[Katiris]

I have a query that I converted a date field to a varchar so I could eliminate the time stamp but when I use the cf_csv_to_file csv_query custom tag the date gets changed from

6/6/05 to 6/06/2005 in the csv file. Can anyone help me with this?

 

[TruthInSatire]

is that the actual value in the CSV file (view in note pad) or the view in excel? Excel will format dates depending on how you have them formated.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[Katiris]

When I open it with notepad it is 06/06/05 - so now I'm really confused. I'm new to this so I'm not sure whats happening.

 

[TruthInSatire]

note pad will show you exactly what CF is producing. like when you view source of a cfm page. If you're using excel to view the data, excel will show the date in the default date format.



We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[Katiris]

Okay I understand that whats confusing now is I can replicate how the old system generates it but I take it to MAS200 and it bombs. Thats whats throwing me. I will try a couple more ideas and see which works out.

 

[Katiris]

The only format that "our" MAS200 will except those dates are 6/6/05 I need to be able to suppress the leading zero's if you can help with that I would be in business. Thank you for your help today.

 

[joepeacock]

We may not all be familiar with the custom tag you are using. Can you post the relevant code that generates the CSV file, or updates it?

 

[Katiris]

<cf_csv_to_file csv_query = "SELECT InvoiceNumber, TypeChk, ChartofAccounts,
Bank_name, Address1, Address2, Address3,
City, State, Zip, CheckDate,
Borrowername, Sellername,
SearchedPropertyAddress,
City1, State1, Zip1,
ChkDate,
Amount, Description, Payee
FROM temp_checkdetails_csv_final"
csv_query_datasource = "Accutrac"
csv_table_headers = "Invoice Number|Type|Chart of Accounts|Bank Name|Address1|Address2|Address3|City|State|Zip|CheckDate|Borrowername|Sellername|SearchedPropertyAddress|City1|State1|Zip1|Date|Amount|Description|Payee"
csv_data_fields = "InvoiceNumber|TypeChk|ChartofAccounts|Bank_Name|Address1|Address2|Address3|City|State|Zip|CheckDate|Borrowername|Sellername|SearchedPropertyAddress|City1|State1|Zip1|ChkDate|Amount|Description|Payee"
csv_filepath="#directory#\#order#">

There is also a .cfm file that I have that is required in my directory to run it, but its encrypted. I got it from a site my supervisor used for custom tags.

 

[joepeacock]

Sounds like maybe the relevant code is that CFM tag file you have. That's the code that is actually formatting your data. If it's encrypted, you may be SOL. What is the source database? If it is SQLServer, you may try something like this for your csv_query value:

SELECT InvoiceNumber, TypeChk, ChartofAccounts,
Bank_name, Address1, Address2, Address3,
City, State, Zip, CheckDate,
Borrowername, Sellername,
SearchedPropertyAddress,
City1, State1, Zip1,
month(chkdate) + '/' + day(chkdate) + '/' + right(year(chkdate),2) AS ckd,
Amount, Description, Payee

If it's another RDBMS, you may be able to find the correct syntax to do the same basic thing. If you are pulling data from a CSV originaly, I'm not sure if you'll be able to do this.

 

[ecobb]

Um...since CF is formatting the data correctly, the CSV file formats the data correctly, and Excel shows the data incorrectly (with leading digits)...wouldn't you just format the column in Excel to display the value the way that MAS wants it? Or maybe don't use Excel at all, just have the CSV file got straight to MAS.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[Katiris]

Yes I can have excel format the file. The object of the whole processes is so that no one has to do anything expect import the file into MAS200. At this point I can send the data over from the database like 6/06/05. I need to get the format to be 6/6/05 to be correct. You can't for some reason do any formatting within the csv_query so it has to be totally correct before it's written to the csv file. And I have no access to the MAS200 -

 

[TruthInSatire]

why can't you do this without a custom tag? i make CSV's all the time. It's a whole lot easier than you think.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[Katiris]

As I indicated to you I'm new to this area. We are still only using CF4.5. That's why I used the tag. If there is another way please feel free to share. Up until this time I never had to do this type of thing. I'm open to learning. Thanks...K

 

[TruthInSatire]

A keyword search in this area brings up a few threads. one of them is thread232-1057039 I know there are some others but can't find them, this should help you get going though.

My key word search was "csv cffile"

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[Katiris]

Thanks I'll give it a try!