Bulk insert into Excel file

[bullpit]

Hi all,
I am trying to perform a bulk insert into an Excel file. I have a csv file
with data that I want to insert into an existing Excel file (a blank
template). The template has some rows in the beginning that I want to skip
and then start writing into it.
I came across this:

BULK INSERT OrdersBulk
FROM 'c:\file.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

This SQL skips the first row in the CSV file and starts reading from the
second row.
Can I have similar functionality while writing into an excel file to skip
initial rows and then start writing?
Please help...

 

[bullpit]

Hi all,
I am trying to perform a bulk insert into an Excel file. I have a csv file
with data that I want to insert into an existing Excel file (a blank
template). The template has some rows in the beginning that I want to skip
and then start writing into it.
I came across this:

BULK INSERT OrdersBulk
FROM 'c:\file.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

This SQL skips the first row in the CSV file and starts reading from the
second row.
Can I have similar functionality while writing into an excel file to skip
initial rows and then start writing?
Please help...

I think I partially got it. I found out that I can define a region in excel
file and then reference that region instead of the whole sheet.
But I am still open for inputs and suggestions...
thanks
bullpit

 

[bullpit]

I think I partially got it. I found out that I can define a region in excel
file and then reference that region instead of the whole sheet.
But I am still open for inputs and suggestions...

I am stuck again. I can use the above solution to add a row the range but
when I try to add another row, the application gives me an error
"Cannot expand the named range"

What now?

 

[Cindy M.]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

I think I partially got it. I found out that I can define a region in excel
file and then reference that region instead of the whole sheet.
But I am still open for inputs and suggestions...

I am stuck again. I can use the above solution to add a row the range but
when I try to add another row, the application gives me an error
"Cannot expand the named range"

I should think you'd need to redefine the range name in your code to include
the range you're adding. Or define the original range with an extra (empty)
line at the end, then insert your range before that line. That should extend
the range automatically (as long as there's nothing below it).

You might want to discuss this question in the more specialized
excel.programming newsgroup.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[bullpit]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

I think I partially got it. I found out that I can define a region in excel
file and then reference that region instead of the whole sheet.
But I am still open for inputs and suggestions...

I am stuck again. I can use the above solution to add a row the range but
when I try to add another row, the application gives me an error
"Cannot expand the named range"

I should think you'd need to redefine the range name in your code to include
the range you're adding. Or define the original range with an extra (empty)
line at the end, then insert your range before that line. That should extend
the range automatically (as long as there's nothing below it).

You might want to discuss this question in the more specialized
excel.programming newsgroup.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

Before reading your response, I was trying things myself and came up with a
solution which I believe is similar to yours when you say define a range with
1 extra line. What I do is I start an integer counter in my code which is set
to the number of rows I want to skip initially (the header). Then whenever I
add a row in Excel sheet from my code, I increment the counter by one and
refer to the incremented range every time. This way, it keeps adding a blank
line to the range. I believe this is what you meant but from excel side.

Something like this:
INSERT INTO [Product_Pricing$C" + i + "" + i + "] VALUES ('" +
dataGridView1.Rows[rowNumber].Cells[2].Value.ToString() + "','" +
dataGridView1.Rows[rowNumber].Cells[3].Value.ToString() + "')"

Thanks a lot...you have been of a great help.

 

[Cindy M.]

Thanks a lot...you have been of a great help.You're welcome. Considering I'm actually a Word and not an
Excel specialist, I'm quite tickled I was able to help at
all!

-- Cindy Meister

 

[bullpit]

I should think you'd need to redefine the range name in your code to include

the range you're adding. Or define the original range with an extra (empty)
line at the end, then insert your range before that line. That should extend
the range automatically (as long as there's nothing below it).

Can you please expand on this? What exactly should I do?