Graphics

Using SQL Queries to Automatically Order External Data Dynamically | Ross Video

Hi, I found the preceding video and was hoping to use it as a guide to pre sort some data for graphics. My datalinq is looking at a .txt formatted CSV file with a header row. I hope this is something simple, but I don't have a table name so I can't seem to get an SQL query to work following the tutorial as a guide.

Any help would be great appreciated, thanks!

------------------------------
Nathan S.
------------------------------

Solved my own question.

In my case the file is saved as CandidateInfo.txt, so it looks like I was able to reference the table by the file name without extension. 

SELECT * FROM CandidateInfo ORDER BY Votes DESC

Let me know if there is a better way to do this, thanks!

Edit: This isn't working. See my next post.

------------------------------
Nathan S.
------------------------------

Original Message:
Sent: 07-14-2021 10:11
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Using SQL Queries to Automatically Order External Data Dynamically | Ross Video

Hi, I found the preceding video and was hoping to use it as a guide to pre sort some data for graphics. My datalinq is looking at a .txt formatted CSV file with a header row. I hope this is something simple, but I don't have a table name so I can't seem to get an SQL query to work following the tutorial as a guide.

Any help would be great appreciated, thanks!

------------------------------
Nathan S.
------------------------------

Shoot! I did a bad job testing this. Turns it it ISN'T working. My table just happened to already be sorted. Could someone please help!

------------------------------
Nathan S.
------------------------------

Original Message:
Sent: 07-14-2021 10:30
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Solved my own question.

In my case the file is saved as CandidateInfo.txt, so it looks like I was able to reference the table by the file name without extension. 

SELECT * FROM CandidateInfo ORDER BY Votes DESC

Let me know if there is a better way to do this, thanks!

------------------------------
Nathan S.

Original Message:
Sent: 07-14-2021 10:11
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Using SQL Queries to Automatically Order External Data Dynamically | Ross Video

Hi, I found the preceding video and was hoping to use it as a guide to pre sort some data for graphics. My datalinq is looking at a .txt formatted CSV file with a header row. I hope this is something simple, but I don't have a table name so I can't seem to get an SQL query to work following the tutorial as a guide.

Any help would be great appreciated, thanks!

------------------------------
Nathan S.
------------------------------

Digging around on the forums, it seems that SQL queries only work with ADODB sources. Is this correct?

On that track, I opened the csv file in excel and exported it as an .xlsx file, which I used with an ADODB datalinq. This was fine, but I had no table to select in expression so I resorted to creating a dummy worksheet in my excel file. This allowed me to see and select a table, and sorting did work.

This can't be a solution for me though, as I can't automate creating an excel file from a CSV. I can't be the first person to want to sort a csv in expression. What is the proper solution here? Again, please help.

------------------------------
Nathan S.
------------------------------

Original Message:
Sent: 07-14-2021 10:11
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Using SQL Queries to Automatically Order External Data Dynamically | Ross Video

Hi, I found the preceding video and was hoping to use it as a guide to pre sort some data for graphics. My datalinq is looking at a .txt formatted CSV file with a header row. I hope this is something simple, but I don't have a table name so I can't seem to get an SQL query to work following the tutorial as a guide.

Any help would be great appreciated, thanks!

------------------------------
Nathan S.
------------------------------

The microsoft ADODB JET driver should be able to read .CSV files also, which would allow sorting.
Try putting the CSV file into a folder and setting it in the connection string like this (d:\CSV in my example):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\csv;Extended Properties="text;HDR=Yes;FMT=Delimited";

You should be able to then use SQL to sort the table.




------------------------------
Brian Ford
Ross Video
------------------------------

Original Message:
Sent: 07-14-2021 15:42
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Digging around on the forums, it seems that SQL queries only work with ADODB sources. Is this correct?

On that track, I opened the csv file in excel and exported it as an .xlsx file, which I used with an ADODB datalinq. This was fine, but I had no table to select in expression so I resorted to creating a dummy worksheet in my excel file. This allowed me to see and select a table, and sorting did work.

This can't be a solution for me though, as I can't automate creating an excel file from a CSV. I can't be the first person to want to sort a csv in expression. What is the proper solution here? Again, please help.

------------------------------
Nathan S.

Original Message:
Sent: 07-14-2021 10:11
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Using SQL Queries to Automatically Order External Data Dynamically | Ross Video

Hi, I found the preceding video and was hoping to use it as a guide to pre sort some data for graphics. My datalinq is looking at a .txt formatted CSV file with a header row. I hope this is something simple, but I don't have a table name so I can't seem to get an SQL query to work following the tutorial as a guide.

Any help would be great appreciated, thanks!

------------------------------
Nathan S.
------------------------------

Thanks, Brian! After some quick tests, I think I'll be able to get this to work.

For anyone else trying to do this, I will summarize:

The key to making this work is to make sure that you:
1) Have a folder that contains your CSV file, and
2) The datalinq must be configured as so:
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=P:\Election_Results\ResultsXpression\csv;
Extended Properties="text;HDR=Yes;FMT=Delimited";
Make sure to point Data Source to the folder containing your file.

------------------------------
Nathan S.
------------------------------

Original Message:
Sent: 08-30-2021 12:58
From: Brian Ford
Subject: SQL Query Sort using csv (no/unamed table?)

The microsoft ADODB JET driver should be able to read .CSV files also, which would allow sorting.
Try putting the CSV file into a folder and setting it in the connection string like this (d:\CSV in my example):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\csv;Extended Properties="text;HDR=Yes;FMT=Delimited";

You should be able to then use SQL to sort the table.

------------------------------
Brian Ford
Ross Video

Original Message:
Sent: 07-14-2021 15:42
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Digging around on the forums, it seems that SQL queries only work with ADODB sources. Is this correct?

On that track, I opened the csv file in excel and exported it as an .xlsx file, which I used with an ADODB datalinq. This was fine, but I had no table to select in expression so I resorted to creating a dummy worksheet in my excel file. This allowed me to see and select a table, and sorting did work.

This can't be a solution for me though, as I can't automate creating an excel file from a CSV. I can't be the first person to want to sort a csv in expression. What is the proper solution here? Again, please help.

------------------------------
Nathan S.

Original Message:
Sent: 07-14-2021 10:11
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Using SQL Queries to Automatically Order External Data Dynamically | Ross Video

Hi, I found the preceding video and was hoping to use it as a guide to pre sort some data for graphics. My datalinq is looking at a .txt formatted CSV file with a header row. I hope this is something simple, but I don't have a table name so I can't seem to get an SQL query to work following the tutorial as a guide.

Any help would be great appreciated, thanks!

------------------------------
Nathan S.
------------------------------

Hi Nathan, thank you for your post, it helped me a lot. I want to ask you if you had any issue using this DB driver (with a csv file). In my case, i found that SQL queries were lot more time consuming than other drivers, causing a large latency and errors when graphics were on air.

Thanks.

Nicolás

------------------------------
Nicol?°s No?©
------------------------------

Original Message:
Sent: 09-02-2021 13:15
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Thanks, Brian! After some quick tests, I think I'll be able to get this to work.

For anyone else trying to do this, I will summarize:

The key to making this work is to make sure that you:
1) Have a folder that contains your CSV file, and
2) The datalinq must be configured as so:
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=P:\Election_Results\ResultsXpression\csv;
Extended Properties="text;HDR=Yes;FMT=Delimited";
Make sure to point Data Source to the folder containing your file.

3) The table name will be the file name, so If the file is named CandidateInfo.csv then the table name will be [CandidateInfo#csv] in Xpression.

4) The queries will be in the following format:

SELECT * FROM [CandidateInfo#csv] ORDER BY Votes DESC

where the table is the file name followed by a hashtag and csv, and Votes from my example is the name of the column (defined in the header row of your .csv) that you want to sort by.

In my example I was able to presort the data in the datalinq configuration. By the time I looked at it in Xpression, row 1 was always the person with the most votes, and row 2 had the second-most votes, etc. in descending order.

------------------------------
Nathan S.

Original Message:
Sent: 08-30-2021 12:58
From: Brian Ford
Subject: SQL Query Sort using csv (no/unamed table?)

The microsoft ADODB JET driver should be able to read .CSV files also, which would allow sorting.
Try putting the CSV file into a folder and setting it in the connection string like this (d:\CSV in my example):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\csv;Extended Properties="text;HDR=Yes;FMT=Delimited";

You should be able to then use SQL to sort the table.

------------------------------
Brian Ford
Ross Video

Original Message:
Sent: 07-14-2021 15:42
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Digging around on the forums, it seems that SQL queries only work with ADODB sources. Is this correct?

On that track, I opened the csv file in excel and exported it as an .xlsx file, which I used with an ADODB datalinq. This was fine, but I had no table to select in expression so I resorted to creating a dummy worksheet in my excel file. This allowed me to see and select a table, and sorting did work.

This can't be a solution for me though, as I can't automate creating an excel file from a CSV. I can't be the first person to want to sort a csv in expression. What is the proper solution here? Again, please help.

------------------------------
Nathan S.

Original Message:
Sent: 07-14-2021 10:11
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Using SQL Queries to Automatically Order External Data Dynamically | Ross Video

Hi, I found the preceding video and was hoping to use it as a guide to pre sort some data for graphics. My datalinq is looking at a .txt formatted CSV file with a header row. I hope this is something simple, but I don't have a table name so I can't seem to get an SQL query to work following the tutorial as a guide.

Any help would be great appreciated, thanks!

------------------------------
Nathan S.
------------------------------

May be an odd question, but could something like this also be done for XML files?

------------------------------
Mike DeMarco
Video Production Engineer
RCN
Las Vegas United States
------------------------------

Original Message:
Sent: 08-30-2021 12:58
From: Brian Ford
Subject: SQL Query Sort using csv (no/unamed table?)

The microsoft ADODB JET driver should be able to read .CSV files also, which would allow sorting.
Try putting the CSV file into a folder and setting it in the connection string like this (d:\CSV in my example):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\csv;Extended Properties="text;HDR=Yes;FMT=Delimited";

You should be able to then use SQL to sort the table.

------------------------------
Brian Ford
Ross Video

Original Message:
Sent: 07-14-2021 15:42
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Digging around on the forums, it seems that SQL queries only work with ADODB sources. Is this correct?

On that track, I opened the csv file in excel and exported it as an .xlsx file, which I used with an ADODB datalinq. This was fine, but I had no table to select in expression so I resorted to creating a dummy worksheet in my excel file. This allowed me to see and select a table, and sorting did work.

This can't be a solution for me though, as I can't automate creating an excel file from a CSV. I can't be the first person to want to sort a csv in expression. What is the proper solution here? Again, please help.

------------------------------
Nathan S.

Original Message:
Sent: 07-14-2021 10:11
From: Nathan S.
Subject: SQL Query Sort using csv (no/unamed table?)

Using SQL Queries to Automatically Order External Data Dynamically | Ross Video

Hi, I found the preceding video and was hoping to use it as a guide to pre sort some data for graphics. My datalinq is looking at a .txt formatted CSV file with a header row. I hope this is something simple, but I don't have a table name so I can't seem to get an SQL query to work following the tutorial as a guide.

Any help would be great appreciated, thanks!

------------------------------
Nathan S.
------------------------------