RE: Best way to read in Excel/CSV files

[Peterson, Chris]

OK, like I said I was in mid-process here, but maybe it will help
someone.  You may need to tweak application.cfc before it will run for
you because I have this plugged into a login framework, but let me know
if this gives you any idea's!

http://www.cfcode.net/CompareExcel.zip

Chris 

-Original Message-
From: Andy Matthews [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 18, 2006 4:14 PM
To: CF-Talk
Subject: RE: Best way to read in Excel/CSV files

I'd be interested in that code too, if you're sharing.




~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264383
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Best way to read in Excel/CSV files

[Andy Matthews]

I'd be interested in that code too, if you're sharing.



-Original Message-
From: Peterson, Chris [mailto:[EMAIL PROTECTED]
Sent: Monday, December 18, 2006 3:04 PM
To: CF-Talk
Subject: RE: Best way to read in Excel/CSV files


Christopher,

I started something to do exactly what you are talking about, only I was
creating it to be variable.  So, and end user would upload 2 excel
files, and on the next screen they would choose which columns they
wanted to compare.  Then they would choose what type of comparison (only
matching, non matching, etc).  Then it would spit out to the screen, or
excel, or pdf, etc.  I cannot promise this will work perfect, but I have
some mid-project code if you wanted to peek?

Chris

-Original Message-
From: Christopher Jordan [mailto:[EMAIL PROTECTED]
Sent: Monday, December 18, 2006 4:02 PM
To: CF-Talk
Subject: Re: Best way to read in Excel/CSV files

That's cool, Chris! I didn't realize you could do that. Shows how many
times *I've* had to query at excel table. :o)

Cheers,
Chris

Peterson, Chris wrote:
> Actually, you can specify the excel file you want right in the query,
> like this:
>
>
>   SELECT
>   [INVOICE AMOUNT] as InvAmount,
>   [INVOICE NUMBER] as InvNumber,
>   [ORDER NUMBER] as OrderNum,
>   [PAID AMOUNT] as PaidAmount,
>   [BALANCE DUE] as BalDue,
>   [COMMENTS] as Comment
>   FROM
>   `data$`
>   IN
>   'd:\webroot\reports.net\baldue\temp\#cffile.serverFile#'
> 'EXCEL 5.0;'
>   WHERE
>   [ORDER NUMBER] > 0
>
> 'data$' is the workbook name inside your spreadsheet, and the rest
> should be obvious =)
>
> So, just setup your DSN as a generic Excel ODBC datasource (I named
mine
> Excel, aint I creative?) and query away!
>
> Chris Peterson
>
> -Original Message-
> From: Christopher Jordan [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 18, 2006 3:40 PM
> To: CF-Talk
> Subject: Re: Best way to read in Excel/CSV files
>
> I'd connect to it as a data source, I think. It's just less for the
> client to do. Of course, the file will have to be named the same thing

> each time, else you'd have to create a new DSN each time for each
> differently named file.
>
> Check out their type 4 JDBC Excel driver <http://hxtt.com/excel.html>
> available on www.HXTT.com <http://hxtt.com>. I'm currently using their

> Type 4 DBF driver for visual FoxPro, and it works like a champ. The
> developers, are very ready to help any of their users via email. I've
> never used their text drivers, but I imagine they're not too hard to
get
>
> the hang of.
>
> Hope this helps.
>
> Cheers,
> Chris
>
> Andy Matthews wrote:
>
>> I have a client who wants me to build a quick admin tool for him. It
>>
> would
>
>> allow him to upload 2 files from different dates and list the
>>
> differences.
>
>> It's bascially two Excel files with 4 columns each and about 9000
>>
> lines
>
>> apiece.
>>
>> So my question is, would it be better to connect to the Excel file as
>>
> a
>
>> datasource or have him convert to a CSV text file and upload that
way?
>>
> He'll
>
>> be using this tool each day to compare the current day's file to the
>> previous day's file.
>>
>> > andy matthews
>> web developer
>> certified advanced coldfusion programmer
>> ICGLink, Inc.
>> [EMAIL PROTECTED]
>> 615.370.1530 x737
>> --//->
>>
>>
>>
>>
>
>
>
>





~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264382
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Best way to read in Excel/CSV files

[Christopher Jordan]

sure. why not? I've got my back up against a deadline, but it's always 
nice to see what other folks are doing. Never know when I might have to 
do something similar. :o)

Cheers,
Chris

Peterson, Chris wrote:
> Christopher,
>
> I started something to do exactly what you are talking about, only I was
> creating it to be variable.  So, and end user would upload 2 excel
> files, and on the next screen they would choose which columns they
> wanted to compare.  Then they would choose what type of comparison (only
> matching, non matching, etc).  Then it would spit out to the screen, or
> excel, or pdf, etc.  I cannot promise this will work perfect, but I have
> some mid-project code if you wanted to peek?
>
> Chris 
>
> -Original Message-
> From: Christopher Jordan [mailto:[EMAIL PROTECTED] 
> Sent: Monday, December 18, 2006 4:02 PM
> To: CF-Talk
> Subject: Re: Best way to read in Excel/CSV files
>
> That's cool, Chris! I didn't realize you could do that. Shows how many 
> times *I've* had to query at excel table. :o)
>
> Cheers,
> Chris
>
> Peterson, Chris wrote:
>   
>> Actually, you can specify the excel file you want right in the query,
>> like this:
>>
>>
>>  SELECT
>>  [INVOICE AMOUNT] as InvAmount,
>>  [INVOICE NUMBER] as InvNumber,
>>  [ORDER NUMBER] as OrderNum,
>>  [PAID AMOUNT] as PaidAmount,
>>  [BALANCE DUE] as BalDue,
>>  [COMMENTS] as Comment
>>  FROM
>>  `data$`
>>  IN
>>  'd:\webroot\reports.net\baldue\temp\#cffile.serverFile#'
>> 'EXCEL 5.0;'
>>  WHERE
>>  [ORDER NUMBER] > 0 
>>
>> 'data$' is the workbook name inside your spreadsheet, and the rest
>> should be obvious =)
>>
>> So, just setup your DSN as a generic Excel ODBC datasource (I named
>> 
> mine
>   
>> Excel, aint I creative?) and query away!
>>
>> Chris Peterson
>>
>> -Original Message-
>> From: Christopher Jordan [mailto:[EMAIL PROTECTED] 
>> Sent: Monday, December 18, 2006 3:40 PM
>> To: CF-Talk
>> Subject: Re: Best way to read in Excel/CSV files
>>
>> I'd connect to it as a data source, I think. It's just less for the 
>> client to do. Of course, the file will have to be named the same thing
>> 
>
>   
>> each time, else you'd have to create a new DSN each time for each 
>> differently named file.
>>
>> Check out their type 4 JDBC Excel driver <http://hxtt.com/excel.html> 
>> available on www.HXTT.com <http://hxtt.com>. I'm currently using their
>> 
>
>   
>> Type 4 DBF driver for visual FoxPro, and it works like a champ. The 
>> developers, are very ready to help any of their users via email. I've 
>> never used their text drivers, but I imagine they're not too hard to
>> 
> get
>   
>> the hang of.
>>
>> Hope this helps.
>>
>> Cheers,
>> Chris
>>
>> Andy Matthews wrote:
>>   
>> 
>>> I have a client who wants me to build a quick admin tool for him. It
>>> 
>>>   
>> would
>>   
>> 
>>> allow him to upload 2 files from different dates and list the
>>> 
>>>   
>> differences.
>>   
>> 
>>> It's bascially two Excel files with 4 columns each and about 9000
>>> 
>>>   
>> lines
>>   
>> 
>>> apiece.
>>>
>>> So my question is, would it be better to connect to the Excel file as
>>> 
>>>   
>> a
>>   
>> 
>>> datasource or have him convert to a CSV text file and upload that
>>>   
> way?
>   
>>> 
>>>   
>> He'll
>>   
>> 
>>> be using this tool each day to compare the current day's file to the
>>> previous day's file.
>>>
>>> >> andy matthews
>>> web developer
>>> certified advanced coldfusion programmer
>>> ICGLink, Inc.
>>> [EMAIL PROTECTED]
>>> 615.370.1530 x737
>>> --//->
>>>
>>>
>>>
>>> 
>>>   
>>
>>
>> 
>
>
>
> 

~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264381
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Best way to read in Excel/CSV files

[Peterson, Chris]

Christopher,

I started something to do exactly what you are talking about, only I was
creating it to be variable.  So, and end user would upload 2 excel
files, and on the next screen they would choose which columns they
wanted to compare.  Then they would choose what type of comparison (only
matching, non matching, etc).  Then it would spit out to the screen, or
excel, or pdf, etc.  I cannot promise this will work perfect, but I have
some mid-project code if you wanted to peek?

Chris  

-Original Message-
From: Christopher Jordan [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 18, 2006 4:02 PM
To: CF-Talk
Subject: Re: Best way to read in Excel/CSV files

That's cool, Chris! I didn't realize you could do that. Shows how many 
times *I've* had to query at excel table. :o)

Cheers,
Chris

~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264380
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: Best way to read in Excel/CSV files

[Peterson, Chris]

Christopher,

I started something to do exactly what you are talking about, only I was
creating it to be variable.  So, and end user would upload 2 excel
files, and on the next screen they would choose which columns they
wanted to compare.  Then they would choose what type of comparison (only
matching, non matching, etc).  Then it would spit out to the screen, or
excel, or pdf, etc.  I cannot promise this will work perfect, but I have
some mid-project code if you wanted to peek?

Chris 

-Original Message-
From: Christopher Jordan [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 18, 2006 4:02 PM
To: CF-Talk
Subject: Re: Best way to read in Excel/CSV files

That's cool, Chris! I didn't realize you could do that. Shows how many 
times *I've* had to query at excel table. :o)

Cheers,
Chris

Peterson, Chris wrote:
> Actually, you can specify the excel file you want right in the query,
> like this:
>
>
>   SELECT
>   [INVOICE AMOUNT] as InvAmount,
>   [INVOICE NUMBER] as InvNumber,
>   [ORDER NUMBER] as OrderNum,
>   [PAID AMOUNT] as PaidAmount,
>   [BALANCE DUE] as BalDue,
>   [COMMENTS] as Comment
>   FROM
>   `data$`
>   IN
>   'd:\webroot\reports.net\baldue\temp\#cffile.serverFile#'
> 'EXCEL 5.0;'
>   WHERE
>   [ORDER NUMBER] > 0 
>
> 'data$' is the workbook name inside your spreadsheet, and the rest
> should be obvious =)
>
> So, just setup your DSN as a generic Excel ODBC datasource (I named
mine
> Excel, aint I creative?) and query away!
>
> Chris Peterson
>
> -Original Message-----
> From: Christopher Jordan [mailto:[EMAIL PROTECTED] 
> Sent: Monday, December 18, 2006 3:40 PM
> To: CF-Talk
> Subject: Re: Best way to read in Excel/CSV files
>
> I'd connect to it as a data source, I think. It's just less for the 
> client to do. Of course, the file will have to be named the same thing

> each time, else you'd have to create a new DSN each time for each 
> differently named file.
>
> Check out their type 4 JDBC Excel driver <http://hxtt.com/excel.html> 
> available on www.HXTT.com <http://hxtt.com>. I'm currently using their

> Type 4 DBF driver for visual FoxPro, and it works like a champ. The 
> developers, are very ready to help any of their users via email. I've 
> never used their text drivers, but I imagine they're not too hard to
get
>
> the hang of.
>
> Hope this helps.
>
> Cheers,
> Chris
>
> Andy Matthews wrote:
>   
>> I have a client who wants me to build a quick admin tool for him. It
>> 
> would
>   
>> allow him to upload 2 files from different dates and list the
>> 
> differences.
>   
>> It's bascially two Excel files with 4 columns each and about 9000
>> 
> lines
>   
>> apiece.
>>
>> So my question is, would it be better to connect to the Excel file as
>> 
> a
>   
>> datasource or have him convert to a CSV text file and upload that
way?
>> 
> He'll
>   
>> be using this tool each day to compare the current day's file to the
>> previous day's file.
>>
>> > andy matthews
>> web developer
>> certified advanced coldfusion programmer
>> ICGLink, Inc.
>> [EMAIL PROTECTED]
>> 615.370.1530 x737
>> --//->
>>
>>
>>
>> 
>
>
>
> 



~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264379
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Best way to read in Excel/CSV files

[Christopher Jordan]

That's cool, Chris! I didn't realize you could do that. Shows how many 
times *I've* had to query at excel table. :o)

Cheers,
Chris

Peterson, Chris wrote:
> Actually, you can specify the excel file you want right in the query,
> like this:
>
>
>   SELECT
>   [INVOICE AMOUNT] as InvAmount,
>   [INVOICE NUMBER] as InvNumber,
>   [ORDER NUMBER] as OrderNum,
>   [PAID AMOUNT] as PaidAmount,
>   [BALANCE DUE] as BalDue,
>   [COMMENTS] as Comment
>   FROM
>   `data$`
>   IN
>   'd:\webroot\reports.net\baldue\temp\#cffile.serverFile#'
> 'EXCEL 5.0;'
>   WHERE
>   [ORDER NUMBER] > 0 
>
> 'data$' is the workbook name inside your spreadsheet, and the rest
> should be obvious =)
>
> So, just setup your DSN as a generic Excel ODBC datasource (I named mine
> Excel, aint I creative?) and query away!
>
> Chris Peterson
>
> -Original Message-----
> From: Christopher Jordan [mailto:[EMAIL PROTECTED] 
> Sent: Monday, December 18, 2006 3:40 PM
> To: CF-Talk
> Subject: Re: Best way to read in Excel/CSV files
>
> I'd connect to it as a data source, I think. It's just less for the 
> client to do. Of course, the file will have to be named the same thing 
> each time, else you'd have to create a new DSN each time for each 
> differently named file.
>
> Check out their type 4 JDBC Excel driver <http://hxtt.com/excel.html> 
> available on www.HXTT.com <http://hxtt.com>. I'm currently using their 
> Type 4 DBF driver for visual FoxPro, and it works like a champ. The 
> developers, are very ready to help any of their users via email. I've 
> never used their text drivers, but I imagine they're not too hard to get
>
> the hang of.
>
> Hope this helps.
>
> Cheers,
> Chris
>
> Andy Matthews wrote:
>   
>> I have a client who wants me to build a quick admin tool for him. It
>> 
> would
>   
>> allow him to upload 2 files from different dates and list the
>> 
> differences.
>   
>> It's bascially two Excel files with 4 columns each and about 9000
>> 
> lines
>   
>> apiece.
>>
>> So my question is, would it be better to connect to the Excel file as
>> 
> a
>   
>> datasource or have him convert to a CSV text file and upload that way?
>> 
> He'll
>   
>> be using this tool each day to compare the current day's file to the
>> previous day's file.
>>
>> > andy matthews
>> web developer
>> certified advanced coldfusion programmer
>> ICGLink, Inc.
>> [EMAIL PROTECTED]
>> 615.370.1530 x737
>> --//->
>>
>>
>>
>> 
>
>
>
> 

~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264373
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Best way to read in Excel/CSV files

[Peterson, Chris]

Actually, you can specify the excel file you want right in the query,
like this:


SELECT
[INVOICE AMOUNT] as InvAmount,
[INVOICE NUMBER] as InvNumber,
[ORDER NUMBER] as OrderNum,
[PAID AMOUNT] as PaidAmount,
[BALANCE DUE] as BalDue,
[COMMENTS] as Comment
FROM
`data$`
IN
'd:\webroot\reports.net\baldue\temp\#cffile.serverFile#'
'EXCEL 5.0;'
WHERE
[ORDER NUMBER] > 0 

'data$' is the workbook name inside your spreadsheet, and the rest
should be obvious =)

So, just setup your DSN as a generic Excel ODBC datasource (I named mine
Excel, aint I creative?) and query away!

Chris Peterson

-Original Message-
From: Christopher Jordan [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 18, 2006 3:40 PM
To: CF-Talk
Subject: Re: Best way to read in Excel/CSV files

I'd connect to it as a data source, I think. It's just less for the 
client to do. Of course, the file will have to be named the same thing 
each time, else you'd have to create a new DSN each time for each 
differently named file.

Check out their type 4 JDBC Excel driver <http://hxtt.com/excel.html> 
available on www.HXTT.com <http://hxtt.com>. I'm currently using their 
Type 4 DBF driver for visual FoxPro, and it works like a champ. The 
developers, are very ready to help any of their users via email. I've 
never used their text drivers, but I imagine they're not too hard to get

the hang of.

Hope this helps.

Cheers,
Chris

Andy Matthews wrote:
> I have a client who wants me to build a quick admin tool for him. It
would
> allow him to upload 2 files from different dates and list the
differences.
> It's bascially two Excel files with 4 columns each and about 9000
lines
> apiece.
>
> So my question is, would it be better to connect to the Excel file as
a
> datasource or have him convert to a CSV text file and upload that way?
He'll
> be using this tool each day to compare the current day's file to the
> previous day's file.
>
>  andy matthews
> web developer
> certified advanced coldfusion programmer
> ICGLink, Inc.
> [EMAIL PROTECTED]
> 615.370.1530 x737
> --//->
>
>
> 



~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264364
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Best way to read in Excel/CSV files

[Christopher Jordan]

I'd connect to it as a data source, I think. It's just less for the 
client to do. Of course, the file will have to be named the same thing 
each time, else you'd have to create a new DSN each time for each 
differently named file.

Check out their type 4 JDBC Excel driver  
available on www.HXTT.com . I'm currently using their 
Type 4 DBF driver for visual FoxPro, and it works like a champ. The 
developers, are very ready to help any of their users via email. I've 
never used their text drivers, but I imagine they're not too hard to get 
the hang of.

Hope this helps.

Cheers,
Chris

Andy Matthews wrote:
> I have a client who wants me to build a quick admin tool for him. It would
> allow him to upload 2 files from different dates and list the differences.
> It's bascially two Excel files with 4 columns each and about 9000 lines
> apiece.
>
> So my question is, would it be better to connect to the Excel file as a
> datasource or have him convert to a CSV text file and upload that way? He'll
> be using this tool each day to compare the current day's file to the
> previous day's file.
>
>  andy matthews
> web developer
> certified advanced coldfusion programmer
> ICGLink, Inc.
> [EMAIL PROTECTED]
> 615.370.1530 x737
> --//->
>
>
> 

~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264360
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4