×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Import columns from one table into another table with dissimilar field layout

Import columns from one table into another table with dissimilar field layout

Import columns from one table into another table with dissimilar field layout

(OP)
I have a permanent template with zero records. Every time I get a new file from my client I push the csv file to sql and my end game is to import that file into my permanent template then export back to csv format in the same field layout all the time.

In Foxpro I just type 'Append From <table> and it finds all the matching field names automatically and populates the table. Can that be done in SQL? I can't find anything but I'm sure I should be able to accomplish it. I don't want to input 68 columns using an insert command. My client files change field layout all the time too so it's important to get the right template before I start the data work so my data is always in a uniform format.

Any help would be so much appreciated. Sorry to ask but after a full day and a half trying different things I'm ready to throw in the towel here!

RE: Import columns from one table into another table with dissimilar field layout

When you do it now ‘by hand’, how do you know which ‘field’ from CSV file goes to which field in your ‘permanent template’ in SQL? Do your CSV files have a header row? If so, is there any correlation of the header row information to your fields’ names in your ‘permanent template’ in SQL?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Import columns from one table into another table with dissimilar field layout

(OP)
Hi Andy,

The field (column) names in both tables are the same but my permanent template has less fields/columns (72 vs 189).

The csv does have header rows. I'm used to working in foxpro so I don't have any issue importing records from one dbf to another one as long as the field names are the same in both dbf files.

RE: Import columns from one table into another table with dissimilar field layout

What did you try?

BCP and format files should lead to a solution: https://learn.microsoft.com/en-us/sql/relational-d...
First create a format file for your permanent import table, then create one for the current CSV format and map the fields. See the next steps section about mapping fields

https://learn.microsoft.com/en-us/sql/relational-d...

Instead of manually rearranging the field rows or XML portions you could write a generator.

Chriss

RE: Import columns from one table into another table with dissimilar field layout

"The field (column) names in both tables are the same"
It looks to me you have 2 tables: TableA and TableB

"my permanent template has less fields/columns"
So, you have another table: "permanent template" table? (TableC...?)

And you also have a CSV file(s) from your clients as well, right?
ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Import columns from one table into another table with dissimilar field layout

Just by the way.

Quote (eboughey1008)

In Foxpro I just type 'Append From <table> and it finds all the matching field names automatically and populates the table.

This only works from DBF to DBF, not appending a csv text file into a DBF.

So actually in VFP you also would need a mapping of field names, you can take the first line of the CSV file as a template for names, taking the field types from your import staging table, but you either need a table for the CSV import as a DBF in the order of the fields of the CSV captions or you - and that's only working with BCP in the SQL Server context - you need a format file that can map the fields of the CSV to the SQL Server table in a single step.

Chriss

RE: Import columns from one table into another table with dissimilar field layout

Other thing SQL Server offers are SSIS (SQL Server Integration Services) formerly known (perhaps already about a decade ago) as DTS (Data Transformation Services) which lets you define data sources of any kind usable with any ODBC driver or OLEDB provider, that also includes CSV as data source file and you might get something going with an Excel OLDB provider that treats the CSV as an Excel file (not with Excel as destination in mind, but the special case of an Excel driver used to deal with CSV, though it's not the real deal XLS(X) spreadsheet format, Excel has the same import path and supports CSV as data source by its drivers, too. Of course also mainly to import CSV.

Well, and if all else fails you could redo the import with VFP and then use SSIS with DBFs as a data source, use a linked server using a Foxpro DBC or a directory of DBFs via VFP ODBC driver.

Then there are newer things like Azure Data Factory, if ou use SQL Server in the context of Azure in the cloud instead of on-premise database.

Chriss

RE: Import columns from one table into another table with dissimilar field layout

And another idea: You could use the first line of the CSV file containing the column names as a list of a SELECT ... INTO <TABLE> statement, which creates a new staging table with the columns in order of the CSV file for simply bulk copy:

CODE

SELECT [fieldnames from 1. line of CSV] FROM dbo.yourpermanenttable INTO dbo.NewStagingtable 

Your permanent table would be the template of the column data types for the dbo.Newstagingtable, which then is designed with all the column names in the order of the CSV file and could take in the CSV in a BULK COPY step. But there's still one thing that makes this complicated, too: As you say the CSV has much more fields than you actually have in your import table (dbo.permanenttable), such a SELECT column-list would cause errors about non-existing columns. You can of course remove them, but it will be a tedious process to go through 189-72=103 error reports of missing column names to arrive at the list of fields your import table can actually provide the column definitions of. All unknown columns would need to be defined as varchar columns, for example, if you still import the CSV without a format file by simply importing all columns.

It's not the first thought and likely not the best anyway, so ou still need to do some work per different CSV format you need to deal with.

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close