Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
cf_csv_to_file csv_query
- Thread starter Katiris
- Start date
TruthInSatire
Programmer
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.
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.
- Thread starter
- #3
TruthInSatire
Programmer
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.
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.
- Thread starter
- #5
- Thread starter
- #6
joepeacock
Programmer
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?
- Thread starter
- #8
<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.
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
Programmer
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.
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.
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
"My work is a game, a very serious game." - M.C. Escher
Hope This Helps!
ECAR
ECAR Technologies, LLC
"My work is a game, a very serious game." - M.C. Escher
- Thread starter
- #11
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
Programmer
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.
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.
- Thread starter
- #13
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
Programmer
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.
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.
- Thread starter
- #15
- Status