SharePoint, .Net, SQL, MS BI, Collaboration and Gadgets

I had come across this task few days ago. Hence, put it here for future reference. The requirement is simple. System need to scan a specific folder and import all files into database. Ideally, these are the steps how I do it:

1. Create a import format file using BCP for BULK INSERT usage
2. Use master..xp_cmdshell to get all the file names in to table variable
3. Loop through the table and import data one by one using OPENROWSET and BULK

Create Sample Payment Table

Run Create_Payment_Table.sql to create a “Payment” Table. The columns must match with columns in CSV file.

Creating an XML Format file

Run

EXEC master..xp_cmdshell ‘bcp yourdbname.dbo.Payment format nul -T -t, -c -x -f C:\Payment.xml’

to create an XML format file for BULK INSERT usage.

Alter Sample Payment Table to add more columns

Run Alter_Payment_Table.sql to add extra columns ID, Filename, BatchID, DateCreated, DateModified for tracking purpose.

How to scan all CSV files in a specific folder?

Let say we expect all CSV files will be drop in c:\inbox folder. So, we will use master..xp_cmdshell with DIR dos command to get all the file names.

Store Proc “usp_import”

Loop through inbox folder and call “usp_import_common” to start import.

First declare a @Files table variable and insert result into @Files

Declare @Files Table ([File] varchar(250) null)

Insert all files’ name to @Files

INSERT @Files ([File])
EXEC master..xp_cmdshell ‘DIR C:\inbox\*.csv /b’

Open cursor to import CSV files one by one via store proc “usp_import_common”.

DECLARE @FILENAME VARCHAR(250)
DECLARE file_cursor CURSOR FOR
SELECT [File] FROM @Files
WHERE [File] NOT IN (‘NULL’,‘File Not Found’)

OPEN file_cursor

FETCH NEXT FROM file_cursor
INTO @FILENAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FullFileName = @FilePath + @FILENAME
–Start Import
EXEC usp_import_common @FullFileName
FETCH NEXT FROM file_cursor
INTO @FILENAME
END
CLOSE file_cursor
DEALLOCATE file_cursor

Store Proc “usp_import_common”

Import data using BULK command with “Payment.xml” as format file

– Set Payment’s table XML format file
SET @FormatFile =‘C:\Payment.xml’

– Get data from file
SET @SQL1 = ” +
‘INSERT Payment’ +
‘(’ +
‘ [FileName],’ +
‘ [InvoiceNo],’ +
‘ [Date],’ +
‘ [Status],’ +
‘ [CheckNumber],’ +
‘ [VoucherNo],’ +
‘ [PaymentAmount],’ +
‘ [BatchID]‘ +
‘) ‘
SET @SQL2 = ” +
‘SELECT’ +
‘ ”’ + @SourceFile + ”’,’ +
‘ TMP.[InvoiceNo],’ +
‘ TMP.[Date],’ +
‘ TMP.[Status], ‘ +
‘ TMP.[CheckNumber],’ +
‘ TMP.[VoucherNo],’ +
‘ TMP.[PaymentAmount],’ +
‘ ”’ + CAST (@BatchID AS VARCHAR(50)) + ”’ ‘ +
‘FROM OPENROWSET(’ +
‘ BULK ”’ + @SourceFile + ”’, ‘ +
‘ FORMATFILE = ”’ + @FormatFile + ”’, ‘ +
‘ FIRSTROW = 2 ) TMP’
EXEC (@SQL1 + @SQL2)

After import, we will delete the CSV:

DECLARE @DirCMD VARCHAR(1000)
SELECT @DirCMD = ‘DEL “‘ + @SourceFile + ‘” /F’
EXEC master..xp_cmdshell @DirCMD

Finally copy testdata.csv file in to c:\inbox and run EXEC usp_import. Then, you are able to import data successfully.

You may download all the sql, xml and csv file here.

Yes! Definitely. Search engine optimization strategies play an important role in website development. Search engines are the starting point for most people when they’re looking for something on the Internet. This is why the most popular websites are search engines and web portals with search functions. Imagine that you are selling T-shirt online. When an Internet user key in “T-shirt” in google search engine. You website appear as top ranking in the search result. This will generate a huge traffic to your web site and off course may generate huge revenue to your company.

There are many statistics that illustrate the importance of search engines as a method of attracting traffic on the Internet:

• 24 percent of marketers said that over three-quarters of their traffic came from search engine referrals. [CyberAtlas Research - Aug 21, 2001]
• Nearly 60 percent of students reported using a search engine when picking an online retailer. [NACS - Mar 7, 2001]
• Study found that 55 percent of online purchases were made on sites found through search listings. [NPD Group -Feb 14, 2001]
• Search engines were used by 86 percent of users in Switzerland. [Wemf AG Research - Ag 31, 2000]
• Over 80 percent of Internet users reach sites through search engines. [Forrester Research - Jul 5, 2000]
• Nearly 48 percent of websites depend on search engine listings for the majority of their traffic and over 70 percent of all websites generate at least 20 percent of their traffic from search engine listings. [NetGambit - July 14, 1997]

Read the rest of this entry »

Internet revolution has been started since early 90’s. Nowadays, I believe even a 15 years old school boy may able to build their own website using HTML language. We can always see some advertisement showing “Web design package – RM 300 for unlimited pages!” in Malaysia. I am a bit curious, how can people build a website with unlimited pages and only cost you RM300? Yes, I know HTML language is easy. However, will there really able to deliver a professional web design look? Is the website search engine friendly? Will they put a great deal of effort into the design? Do they have sufficient skills and experience to deliver what you want? Personally, I don’t think that they can deliver a good looking, professional website with only RM 300. If you know somewhere can get this kind of web design package please do let me know.

I had experience in web development for more than 4 years in Malaysia. I can tell you that the website design marketplace in Malaysia is very bad nowadays. A lot of companies do not want to spend more then RM 1000 for their website. We often heard from our clients that “XXX offer RM 300 for unlimited web pages. Why your packages so expensive?” At the end, we need to give more discounts in order to get the sales. This is the fact for all web design companies in Malaysia. However, I think everybody should know that there is no free lunch in this world. A good quality and successful web site be sure will cost you more compare to a budget website. In return, you will get better image for your company, generate more traffic to your website and increase your revenues. Besides, it can be an extremely effective method of communicating between you and your customer. If you planning go for budget web design package (RM 300), I will suggest you better do not waste this kind of money. Spend it for your staff lunch and hope your staff will work harder and get more sales for you rather than create a lousy, low quality and useless website.

Here are some of my previous projects done when I was a freelancer 2 years ago. http://www.quantum-profile.com. I am no longer work as freelancer now. As you can see, the “RM 300 for unlimited web pages”! It is hard to survive in this marketplace. I am currently working in an MNC software house in KLCC. However, sometime I still helping some friends or relative when they need me in website development…

Welcome! Finally I decided put start up a blog about my techie life. This because i do get a lot of issue in my working life which make me crazy to solve it. So, I will try to post the usefull tips and anything which is interesting. For my own records and may be will help someone out there too….