2

I have a manual process in SQL Server that I would like to automate in Excel VBA. In MS SQL Server 2014, the task is to upload a csv file to a new table in a database. To do this, I right-click on the database that I want, click Tasks / Data Import. This opens up the SQL Server Import and Export Wizard. In there, I choose Flat File Source as the Data Source. Then, I choose the csv file that I want. I tick the checkbox, “Column names in the first data row”. I click next, next and the file creates on the server.

Is there a way to do this from Excel VBA? I’m sure I can create the table in SQL from Excel VBA by passing across a CREATE TABLE command, and then execute a BULK INSERT command, which will pass the data across. However, I’d rather avoid the creation of the table as it would involve specifying the data types of the columns; when the process is invoked from SQL Server, SQL Server does that for you automatically. Can I also do that automatically from Excel VBA?

Improve this question
3
  • If you want to automate it, why not use SSIS? Commented Apr 30, 2018 at 8:33
  • @Larnu, can this be controlled / executed from Excel VBA? I am unfamiliar with SSIS. Commented Apr 30, 2018 at 8:49
  • I wouldn't suggest you do, although you could. Normally you would use the Agent or T-SQL to execute the task in the SSISDB. Commented Apr 30, 2018 at 8:51

1 Answer 1

Reset to default
1

The easiest (but probably not the best way) I can think of:

  • Read the CSV file through Excel-VBA

Excel VBA - How to read csv file line by line but not the whole file

  • Write an CREATE TABLE query with the contents of the file. Define the columns as [VARCHAR].

Create a SQL Table from Excel VBA

I have tried the process you describe with "SQL Server Import and Export Wizard" and I have created a table this way.

Then, after selecting the table and checking the "Script table as" > "CREATE IN", I have noticed the following command:

CREATE TABLE [dbo].[Textdokument](
    ["name1"] [varchar](50) NULL,
    ["name2"] [varchar](50) NULL,
    ["name3"] [varchar](50) NULL,
    ["name4"] [varchar](50) NULL,
    [Spalte 4] [varchar](50) NULL
) ON [PRIMARY]

Honestly, this is not very optimized, as far as I had some columns, which were numbers and varchar is quite "expensive" for them.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

the table doesn't already exist in SQL though. I want to create it from Excel VBA without going to the trouble of specifying the data types of the fields.
@mediaeval - from VBA you can use any SQL command. CREATE TABLE is probably what you need.
@Vityata this smells like you're saying that the Excel file doesn't have a uniform format; is my assumption correct?
@Larnu - nope, excel has a uniform format, but the OP actually asks to create a table through SQL and automatically to assign the columns to the correct data type.
@Vityata, I have repeated what you did in SQL with "Script Table as". It's helpful. I had hoped it would be possible to replicate how it is done in SQL Server by not specifying the data types, but I do not think it is possible. If I could find a way to pass across the option, "Column names in the first data row” (there is a fuller explanation of this in the original post), that would be perfect.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.