MSSQL : Import CSV file into a Table

Recently, I am working on the data stored in the Excel files. Those data are needed to be imported into the database in order to allow the stored procedures to execute and tabulate data back to the users. During the process of importing the Excel files using the “Import Data” (Right click the database > Task), I found the process was tedious because of setting the correct data type for each columns and always cause me to have an error related to truncation text.

Since, the Excel files have a lot of columns and rows, I decided to try another method to import the Excel data into the table, by using the BULK INSERT method. This method requires the Excel files to be in .csv format to begin with. It is easy to convert the .xlsx into .csv file.

Below is the script on how to use the “BULK INSERT” script. Beforehand, a table called dbo.upload_file is created. Based on the delimiter set in the local machine, place the value accordingly at the “FieldTerminator”.

BULK INSERT [dbo].[upload_file] FROM 'C:\Users\<filename>.csv'
    WITH (
       FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n'
 );

The nightmare began when I realized the data in the Excel contains a lot of comma (,). The data cleaning process began by removing them altogether from the Excel file. Besides that, you want to check if you have inserted the header into the database’ table.

We can set the first row to be inserted into the table.

BULK INSERT [dbo].[upload_file] FROM 'C:\Users\<filename>.csv'
    WITH (
       FIRSTROW = 2,
       FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n'
 );

By default, Excel saves the .csv file using delimiter comma (,), however it can be changed at the Control Panel setting. Open up Control Panel –> Region and Language, and then click the Additional settings button on the bottom. Now look very closely at the “List separator” item, which normally has a comma in the field, change it to your preferred delimiter such as pipes “|”. Then, hit “Apply” and “Save” button to save the changes made.

Then, next time when saving a .csv file, it will use the new delimiter. Either way can solve the issues mentioned above and the data can be inserted into the database’s table successfully.

Advertisements

Valentine’s Day

It would be great if I can write something on the Valentine’s Day instead leaving it blank. I just came back from my Chinese New Year break in Malaysia. It was one of the longest breaks in these two years as I did not have think about work during this period.

Live has changed so much and I appreciated everything I am doing and enjoying right now, although, some do not look perfectly good. That does not seem to be a hinder for me to continue do great work in the future.

My Chinese New Year celebration started with sharing the joy and fireworks prepared by my neighbours at my “hometown” upon I reached home. One of moments I captured from my phone.

As usual every morning of the first few days of Chinese New Year, the local Chinese coffee shops would not open for businesses because for most Chinese, they would prepare and cook food from home when all the relatives were around. It was not the same situation for my family. We did not cook much, so each morning, we visited the same place and ordered the same food, which I loved it so much. So, what did I eat for 3 mornings?

The local mamak stall at Kampung Pandan, served the roti telur with teh c kosong. The restaurant is called the Restoran Kampung Pandan Kari Kepala Ikan. It is one of the best curry fish in the KL town.

This year I did not plan for any house visitations or meet up with friends except for a friend who has been away from town for a year. So, there was not much angpaos collected for this year.

Most of the activities at home was sparing my time talking with the elderly, resting mode always and watching the cats passed by to let me take pictures and put some captions before uploading them to Facebook.

Kucing kucing jiran loved to pose.
This was during raining day.

– LiYen

Life has been awesomely good during this period. Simple yet fullness.

What I can ask for more, when my dining table was full with blessing of good food?

I have no ideas why but it did happen. Just about two days before I travelled back to Singapore to start working again, I removed a lot of people from my contact list. They were old friends, schoolmates, collegemates, ex-colleagues and ex-partners who did not keep in touch me for as short as half a year to as long as few years. As time has drifted us apart, I think I do not want to keep them for the sake of keeping when I knew we no longer in touch.

Free up the space, the contact list, the old messages and any memories we used to share, let me move on easily and be opened to accept new people. I am ready to start new beginning and embark a new journey. Hopefully, in the near future, I can share more happy news again.

And, Happy Valentine’s Day everyone.

Day 17: Python Control Flow Using While Loops

While loops will continue to execute a block of codes while some condition remains True. The main keyword is while, colon (:) and indentation (whitespace). The syntax for a while loop looks as simple as below:

# while loop statement
while some_boolean_condition:
# do something

# Examples
x = 0
while x < 5:
print(f'The number is {x}')
x += 1

I believe you can imagine how the output looks like,

When I run the above codes, it begins with a variable, x is being assigned with value 0. The while keyword will check the condition whether x is less than 5. If the condition is True, then it executes the codes after the colon (:). In this example, it prints out the number 0 with x is being formatted as string with f-string method (“f”) , then next line, it does an incremental of value by 1 for the variable, x.

It is important to include the incremental value of variable x within the while loop so that the condition will come to halt otherwise, it turns to be an infinite loop because the condition is always True, x value is always 0, and I have to force the program to terminate itself.

It is a common mistake to miss out this incremental value, so be careful.

Another syntax for the while loops is using the keyword else.

Summary of the day:

  • Control flow using while (condition) loops.
  • Main keywords: while.
  • Dangerous condition is while loops can be infinite if did not handle carefully.

Day 17: List Comprehensions in Python

python programming

Following the online learning website, the instructor shows how to quickly create a list with Python. From what we have learned so far, to create a list, we first declare an empty list and use a for loop with .append() method to create a list. An alternative has been introduced during this section.

Based on the example given by the instructor, I re-used it here. See below for the code.

#sample code

mystring = 'sunday'
mylist = []
for letter in mystring:
mylist.append(letter)
mylist

#Output:
['s', 'u', 'n', 'd', 'a', 'y']

In the instructor example, the codes can be shortened into one single line using the below codes:

#list comprehensions in python

mylist = [letter for letter in mystring]
mylist

#Output is the same:
['s', 'u', 'n', 'd', 'a', 'y']

This makes me recall what my CTO likes to mention to me, it is just one line code. My CTO is referring to the Scala codes.

This thing works pretty amazing!

#Another example

mylist = [x for x in 'hello']
mylist

#Output:
['h', 'e', 'l', 'l', 'o']

You can even define a list of numbers ranging from 0 to 10 with just a little bit amendment from the above codes.

#Sample using numbers.

mylist = [num for num in range(0,11)]
mylist

#Output:
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

In addition to the above codes, I can also do a power of 2 for the variable (“num”) which is showed by the instructor in the video by adding the **2 to compute the values. Besides using exponent, it means we can do some computation to get values too.

#Sample using exponent 

mylist = [num**2 for num in range(0,11)]
mylist

#Output:
[0, 1, 4, 9, 16, 25, 36, 49, 64, 81, 100]

The instructor even deep down into using if statement within the square bracket to check the condition if only even number, then list them out.

#Sample using if statement

mylist = [num**2 for num in range(0,11) if num%2 == 0]
mylist

#output:
[0, 4, 16, 36, 64, 100]

This makes me more excited to continue seeing if the instructor has move tips and tricks to share with us. It can also do it for double for loops. The full codes for the above examples,

  • shorthand in Python

Day 16: Python Control Flow using For Loops

python programming

In Python, some of the objects are iterable which means I can loop through the object in a list for example, to get each element or I can loop through a string to capture each character in the string. The main keywords are for and in. It uses along with colon (:) and indentation (whitespace). Following is the syntax,

#loop statement
my_iterable = [1,2,3]
for item_name in my_iterable:
print(item_name)

my_iterable is a list of number and the keyword “for” begins the for loop, with a given variable name called item_name. So, this for loop has a name called, item_name to represent the elements inside the list later. Next, the second keyword is “in” means it loops inside the variable called my_iterable. And, it follows by colon (:) to tell the program to execute everything after the colon. In this example, it prints the variable, item_name’s values one by one until end of the list.

Using the same list, you can also print something else instead of the elements of the variable, my_iterable. Example,

It works too as the variable, my_iterable works as a counter and prints “Hello” thrice. Then, actually I do not need to have the variable named, num right? Yes, I can remove it with using underscore (_).

In Python, when we do not intend to use the variable name anywhere, we can just put “_“. It works pretty cool.

It is simple to use and it can use with if statement I just shared in previous blog. Look into below example.

When I place the print outside of the for statement (refer to [8]), print() is aligned with for keyword, means it prints the final value of the list_num after the loop ends. However, if I indent the print statement to be aligned with list_num’s sum operation, it prints each sum value of the list_sum until end of the list.

It depends on how do you want to display the information, the indentation of print statement positioning plays a role. If I put the print statement before the for statement, it prints 0 first and follows by the sum values of list_num in each iteration. Just give a try to confirm it 🙂 

Do you get the same output as me?

It works for string too as I mentioned earlier, example if I want to get each character in a string.

Another example which I learned from online learning website is using tuple. Still remember tuple element is inside a parenthesis ()? Below examples are showing how to use tuple and tuple in a list in for statement.

In [1], it uses tuple and prints out the value one by one.
In [2] it uses a list of tuple, in a pair of two and it prints out 4 pairs of tuples.

Here is a jargon to learn in Python, tuple unpacking. It means unpack or extract the values into variable. The next codes shows how to do it. It creates variables in same structure as the tuple and print each variable separately. With this method, it can access each value individually instead. If want to just print variable a, then omit the print(b).

Lastly, we can iterate through a dictionary, another type of collections in Python. Dictionary is unordered mappings, so in a large dataset, we may not able to get the values listed nicely and how to access the dictionary key and value will help.

By default, using for statement in a dictionary, it returns the “key”, that is shown in the [1] example. To access the key-value pair, it uses d.items(), which is shown in the [2] example. And, with this structure, I am sure you know, we can access each element if we use key,value similarly how we try to access tuple earlier.

Fantastic!

Now, I start to think back the time when I looked into the Scala codes, it did has similar things. I am sorry, I am supposed to be a developer who knows how to code Scala but I did not really learn it well. Alright, I am going to stop here and hopefully, if there is more interesting loop examples to be shared in the future, I will update it.

  • Control flow using For loops.
  • Important keywords: for, in.
  • The magic using underscore (_).
  • Tuple unpacking. (Quite interesting topic to look at).

Day 16: Python Control Flow using If, Elif and Else Statements

python programming

When we want to execute the codes with matching certain conditions, If, Elif and Else is being used. It is simple and easy to code. Let me show the syntax. The main keywords are if, elif and else as the logic flow. It uses along with colon (:) and indentation (whitespace). Following is the syntax,

# Syntax for if statement
if x = 1:
print('X has a value.')

# Syntax for if/else statement
if x = 1:
print('X has a value.')
else:
print('X does not have a value.')

# Syntax for multiple conditions
if x = 1:
print('X has a value.')
elif x = a:
print('X has a string.')
else:
print('X does not have a value.')

The if, elif and else keywords have to be aligned in the same indentation in order to work properly. I can have multiple elif statements for different conditions to be matched.

It is a straightforward control flow statement and it is often used with other control flows. Check it out in my next posts.

Summary of the day:

  • Control flow: If, Elif and Else

Day 15: I/O with Basic File in Python

python programming

Some of the I/O operations require us to install certain library. The following is working examples using Jupyter notebook, so it works for Jupyter notebook only. I do not install Jupyter notebook, so I used the browser version from the Jupyter’s website to continue learning.

Let try…

Create a file at Jupyter notebook
Start the syntax with,
%%writefile filename.txt

See below for an example code I did,

Once, I hit the “Run” button, it showed the “Writing myfile.txt”. The file is successfully written. My questions are:
1. Where did the file save since I was using the online browser version?
2. Why is it using the symbols, “%%“? Is it just for Jupyter Notebook?

Is there anyone can give me an advice?

How to access a file?
myfile.txt was created and able to be accessed when I used the following codes, 
myfile = open(‘myfile.txt‘)

Yes, correct the syntax is open(‘<filename.extension>‘)
I will know if my file is able to be accessed or failed, if I did not have the file or typed a wrong filename or it has a wrong file location path, an error will throw on the screen. See the example below:

Check working directory
To know what is the location of current working directory, use the following command to find out. Just type,
pwd

If you are same as me using the Jupyter Notebook from their website, it shall be showing,
‘/home/jovyan/binder’

or it would a file location from your machine.

Read the file
read() is used to read a file. It returns all the contents in the file specified in the syntax. Example:

myfile = open('myfile.txt') 
myfile.read()

Its output:

Because I asked to return everything in the file, it showed the whole strings with “\n“, an escape sequence. It is a indication, or we shall call it, delimiter, to tell me it is a newline in my text file.

If I run again the myfile.read(), its output is not the same anymore. Ops, why? It is because when it reads, it reads until end of the file. And, in Python, it has a cursor at the beginning of a file and when it reads it, it moves all the way to the end of a file. 

I need to reset the cursor back to beginning of the file. How?

Reset the read cursor back to beginning of the file
It uses seek(0). Then, it goes back to the beginning and when I run again myfile = open(‘myfile.txt’), its output same as the 1st time I ran it.

This appears not exactly what we usually see in our browsers correct? I mean you do not want to have the “\n” in your strings correct?

Another read method in Python which reads exactly the format in the text file is using syntax,
readline()

Same way, I write,
myfile.readlines()

It returns a list of elements. I can see its difference between read() and readlines() with the example codes. To read it, I can loop through the list and read each line. The “\n” is remained.

If I am working on a different directory, I can specify the full file location path, as mentioned earlier. One important thing to take note is for Window, the file path has to use double backslash (“\\”) so Python does not treat the second “\” as an escape characters.

For Mac and Linux users, it uses the front slash, just opposite direction slash if you notice the different. Another easy way to rename a file location is using pwd, copy it and change.

Close a file
It is good to make as a habit to close the unused file after reading it. One method we can go with is using the syntax:
close()

Example, myfile.close().

#open, read and close the file
with open('myfile.txt') as my_new_file:
contents = my_new_file.read()

This codes allows the file to close without having to write, myfile.close(). Using the with … it shows the little indent on the second line. This means it will do whatever within this indent and close the file.

Cool, right?

In open() method, there are various modes can be used. Example of modes,

  • mode =’r’ is read only
  • mode =’w’ is write only
  • mode =’r+’ is read and write
  • mode =’a’ is append only. Add something at the end of the file.
  • mode =’w+’ is write and read. Overwrite the existing file.

Summary of the day:
(Based on Jupyter Notebook)

  • how to create a file.
  • how to access a file.
  • how to checking the working directory of a file.
  • how to read, write and close a file.
  • various modes used in the open() method.