78893

Using SQL to query an Excel worksheet without a header row

I have been searching for a solution for a while, and I find myself coming up empty handed.

The question is: Can you build a SQL query against a worksheet if you don't have a distinct header row for column references?

Easily enough:

  |     A     |     B
1 | FirstName | LastName
2 | John      | Davis
3 | Mary      | Parker</pre>

SELECT [LastName] FROM [Sheet1$] WHERE [FirstName] = 'John'</pre>

--> "Davis"

However, I tend to work with header-less CSV files, so what could I do if I don't have the header row to steer by?

  |     A     |     B
1 | John      | Davis
2 | Mary      | Parker</pre>

SELECT ??? FROM [Sheet1$] WHERE ??? = 'John'</pre>

To extend the question, it could equally interesting to know how to reference a row number - if possible - say I want to find the Last Name (column B) relative to row [2]

Thanks in advance for any input on my conundrum!

-B

Answer1:

You can go through this link:

http://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspx

Providing the HDR attribute as NO, will automatically name the columns as F1 to Fn.

Recommend

  • Handling one to many relationship in solr
  • Copy table row and increment all name attributes
  • Oracle regexp match patterns in string that includes new line character
  • How to ensure data integrity in paginated REST API?
  • Selecting distinct, non-null, unless null is the only value for that record combination (tsql)
  • Use same row per thread from a CSV data set in JMeter
  • Compare Dictionary
  • Omit template arguments when create an instance of template class from another instance of template
  • Group By and add columns
  • Tips for creating scalable WPF user control
  • Can statically compiled languages replace scripting language?
  • Can't find cygwin setup.exe file?
  • How to retrieve a specific string from a specific list from a file with JSON in Python
  • Pointer vs Reference difference when passing Eigen objects as arguments
  • How to set `secure` and `httpOnly` for Plones `__ac` cookie?
  • LNK1104: cannot open file 'kernel32.lib'
  • Jquery UI Sortable, move item automatically
  • Haskell: function composition with anonymous/lambda function
  • Get current user from inside the model in Sails
  • Creating a Multidimensional, Associative Array in VBScript
  • apply a javascript function to draggable copy
  • Can my PDF ping my server when it is opened?
  • Aptana 3 remove bundle (jquery)
  • netsh acl setting (need alternative method - registry settings?)
  • Pycharm: Marking a folder as 'sources root' is not recursive for subfolders
  • Bad request using file_get_contents for PUT request in PHP
  • Yii2: Config params vs. const/define
  • How to use an array of arrays with array_map(…) in PHP?
  • Highlight one bar in a series in highcharts?
  • Paperclip, set path outside of rails root folder
  • R - Combining Columns to String Based on Logical Match
  • Get one-time binding to work for ng-if
  • Dynamically accessing properties of knockoutjs observable array
  • Running a C# exe file
  • Do create extension work in single-user mode in postgres?
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • How to load view controller without button in storyboard?