Excel 資料筆數超過 65536 匯入 MS SQL 之方式

在使用 Excel 2003(*.xls)時，有數項條件限制，例如：

• 最大資料列筆數是 65,536 列。Excel 匯出選項會檢查此數值，若超過此限制，會在 Excel 中顯示錯誤。
• 最大資料行數是 256 行。Excel 匯出選項會檢查此數值，若超過此限制，會在 Excel 中顯示錯誤。
  

要解決此問題，可能有幾個方法：

• 當資料筆數會超過 65,536 筆數時，採用新增工作表的方式，讓每個工作表的資料列筆數，不會超過 65,536 筆數的軟體限制。
• 改用 Excel 2007 ，存檔成 *.xlsx 的檔案格式，也可以解決此問題。
  將資料匯出成 *.csv 檔案格式，分隔符號選擇為：[逗號{,}]，再利用 Excel 2007 開啟存取，也可以正常運作。

以下是使用 SSIS ，將資料匯入到 Excel 2003(*.xls)所遇到錯誤：

Excel 2003(*.xls)至多僅能達到 65,536 列。


改用新版本的 Excel 2007(*.xlsx) 也可以改善這些問題，舉例來說：

• 可支援到 1,048,576 筆資料列。
• 可支援到 16,384 資料行。
• Excel 2007 讓試算表的容量擴充到超過 1 百萬資料列和 1 萬 6 千資料行。
  
  

在 Integration Services 中，要與 Excel 2007 連線時，可能要注意以下幾件事情：

• Excel 2007 與 Excel 2003 兩個版本的檔案格式不同，這可能表示我們無法再使用[Microsoft Jet 4.0 OLE DB Provider]來與 Excel 2007 進行連線。
• 在 Microsoft Office 2007 中，發行了新版本的 OLD DB 驅動程式：[Microsoft Office 12.0 Access Database Engine OLE DB Provider]，縮寫為 ACE。

新版本的 ACE 驅動程式，可以稍作屬性的調整，就可以與 Excel 2007 及 Access 2007 連線，請各位參考以下的整理：

• 在[SQL Server 匯入和匯出精靈]中，若是要使用 Excel 2007 作來資料來源或是目的地，則需要利用[Microsoft Office 12.0 Access Database Engine OLE DB Provider]，點選[屬性]，在[資料連結內容]視窗中，點選[全部]頁面，點選[Extended Properties]，輸入以下的資料：Excel 12.0 。
• 在設計 SSIS 封裝時，若是要使用 Excel 2007 作來資料來源或是目的地，需要使用[OLE DB 連接管理員]，然後在[提供者]畫面，選擇[Microsoft Office 12.0 Access Database Engine OLE DB Provider]，請記得，一樣需要設定[Extended Properties]的內容值為：Excel 12.0。
• 至於[Excel 連接管理員]、[Microsoft Jet 4.0 OLE DB Provider]這類的資料庫驅動程式，則仍然適用於與 Excel 2003 (*.xls)進行連線。

筆者提供與利用[SQL Server 匯入和匯出精靈]與 Excel 2007 連線的設定步驟：

0. 請事先利用 Excel 2007 在 C:\ 中，建立 Ex01.xlsx 的檔案。

1. 在資料來源或是目的地的驅動程式部分，選擇[Microsoft Office 12.0 Access Database Engine OLE DB Provider]。

2. 點選[屬性]，在[資料連結內容]畫面，點選[資料來源]，輸入：C:\Ex01.xlsx。




3. 點選[全部]頁面，點選[Extended Properties]，點選[編輯內容值]，輸入以下的資料：Excel 12.0。





4. 再點選回[連線]頁面，點選[測試連線]，正常應該可以看到測試連線成功的訊息，請按下[確定]\[確定]，完成與 Excel 2007 的連線設定，按[下一步]。

若是沒有事先建立 Excel 2007(*.xlsx)檔案時：



如果您的需求是讓 SSIS 於執行時，當下由系統動態創建此 *.xlsx 檔案，無須事先建立此 *.xlsx 檔案。
請將輸入的副檔名修改為 *.xlsb 檔案格式，就可以讓系統自行動態建立此檔案，也能夠正常開啟運作。

Excel 2007 的檔案格式

• Excel 2007 支援多種的檔案格式，依據預設值，副檔名是 *.xlsx，這是一種以 XML 為基礎的全新檔案格式，稱為 Microsoft Office Open XML Formats，具備了數項優點：壓縮檔案、改良的受損檔案復原、輕鬆偵測包含巨集的文件、對個人資訊提供更理想的隱私權保護與更嚴密的控管、更完善地整合並互通商業資料等等，檔案大小也會比(*.xls)來的更小；此外，由於檔案格式屬於 XML 架構格式，再加上開放、免權利金的授權，讓我們開發人員更容易就能建置與採用 Excel 2007 文件內容及中繼資料的解決方案。
• Excel 2007 二進位檔案格式(BIFF12)，其副檔名是：*.xlsb，這是 Excel 2007 專屬的二進位檔案格式(BIFF12)；與先前的版本之二進位檔案格式，副檔名是：*.xls 是不相容的，例如：在 Excel 5.0/95 版本是(BIFF5)，在 Excel 97-2003 版本是(BIFF8)。

參考資料：

Excel 2007 中支援的檔案格式
http://office.microsoft.com/zh-tw/excel/HP100141031028.aspx

將 Office Excel 2007 與舊版 Excel 搭配使用
http://office.microsoft.com/zh-hk/excel/HA100775613076.aspx

新副檔名與 Office XML Formats 簡介
http://office.microsoft.com/zh-tw/help/HA100069351028.aspx

Excel 2007 的規格及限制
http://office.microsoft.com/zh-tw/excel/HP100738491028.aspx

Improving Performance in Excel 2007
http://msdn2.microsoft.com/zh-tw/library/aa730921.aspx#Office2007excelPerf_BigGridIncreasedLimitsExcel

無法匯入超過 65,536 列是文字檔到 Excel 97、 Excel 2000、 Excel 2002 和 Excel 2003
http://support.microsoft.com/kb/120596/

Summary of capability limitations in Excel 97
http://support.microsoft.com/kb/296053/en-us

匯出至 Excel (*.xls)的限制
http://technet.microsoft.com/zh-tw/library/aa337207.aspx

Limitations with Exporting to Excel(*.xls)
http://msdn2.microsoft.com/en-us/library/aa337207.aspx

資料來源：http://blog.csdn.net/Johnson_hejun/archive/2008/11/12/3284582.aspx

Linux 筆記

※ 當 Fedora 9 安裝後要安裝 DB Client 的套件，依序。

rpm -ivh mysql-libs-5.0.51a-1.fc9.i386.rpm

rpm -ivh perl-DBI-1.601-4.fc9.i386.rpm

rpm -ivh mysql-5.0.51a-1.fc9.i386.rpm

rpm -ivh mysql-devel-5.0.51a-1.fc9.i386.rpm

※ 刪除某個資料夾中的所有檔案

rm -r -f /WrittenLogs/*

rm -r -f /SQLLogFiles/*

rm -r -f /LogFiles/*

※ 再 A 伺服器資料夾 (AServer) 中的所有東西丟到 B(90.51) 伺服器的該路徑

scp -r root@10.10.90.51:~/AServer . #最後一個點是必須的

※ Linux 4G 只抓到 2G 的處理方式

yum install kernel-PAE

vi /boot/grub/grub.conf

changr -> default=0

※ 去 B 伺服器指令路徑 (/home/michael)，底下所有 rpm 檔案帶回 A 伺服器的目前路徑 (pwd)

sftp root@10.10.90.51:/home/michael/*.rpm

※ 時間對時及時間排程設定

ntpdate -q 10.10.10.201 #Step.1 先去測試連結建置的 NTP 伺服器測試是否可以通 (Port : 123)

ntpdate 10.10.10.201 #Step.2 確認可以成功，將 NTP上面的時間寫入目前該伺服器的時間

hwclock -r #Step.3 寫入伺服器 BIOS 硬體時間

date #Step.4 確認目前系統的時間是否正確

vi /etc/crontab #Step.5 確認正確後修改排程

09 12 * * * root /usr/sbin/ntpdate 10.10.10.201 && /sbin/hwclock –w #12:09分和 201 對時

※ 新增防火牆允許的 TCP Port 10001

cd /etc/sysconfig/iptables

-A INPUT -m state --state NEW -m tcp -p tcp --dport 10001 -j ACCEPT

※ 刪除某資料夾包含資料夾中所有的檔案

\rm -r /michael/test

※ 刪除某檔案

rm -r 123

※ 刪除某資料夾中的所有檔案

rm -r /michael/test/*.*

※ 將當日所有 Log 壓縮成一份檔案指定名稱的 .tar 檔案

tar -zvc -f t_log2010-08-23.tar t_log2010-08-23.*

※ 變更電腦名稱

/etc/sysconfig/networkˇ

※ 手動設定 Routring 內網 IP 走 eth0

ip route add 10.10.10.0/24 dev eth0

※ 查 Linux 作業系統位元和版本

uname -a -i

※ Linux SNMP Client 操作方式

確認是否有裝 SNMP 服務

rpm -qa | grep snmp

沒有裝請依序裝已下套件

rpm -ivh lm_sensors-3.0.1-5.fc9.i386.rpm
rpm -ivh net-snmp-5.4.1-19.fc9.src.rpm
rpm -ivh net-snmp-5.4.1-14.fc9.i386.rpm

vi /etc/snmp/snmpd.conf

com2sec notConfigUser default public #確認 SNMP 帳號是否為 public

#view mib2 included .iso.org.dod.internet.mgmt.mib-2 fc #將前方#拿掉

access notConfigGroup “” any noauth exact systemview none none
改成：
access notConfigGroup “” any noauth exact mib2 none none

#com2sec mynetwork NETWORK/24 COMMUNITY
改成：

com2sec mynetwork 10.10.10.0/24 public

service snmpd restart

rpm -qa |grep snmp

確認服務啟用： ps -ef|grep snmpd

root 24670 1 0 15:17 ? 00:00:00 /usr/sbin/snmpd Lsd Lf /dev/null -p /var/run/snmpd -a
root 24674 16842 0 15:17 pts/1 00:00:00 grep snmpd

vi /etc/sysconfig/iptables #設定開啟 udp 161

chkconfig snmpd on

chkconfig –list | grep on #查閱是否自動啟動
chkconfig –list | grep snmpd
chkconfig –add snmpd #如果沒有請手動增加

※ 變更檔案權限

chmod -R 755 123.txt

※ find /tmp -name *.log -mtime +1 -exec rm -rf {}

刪除 /tmp 一天以前的檔案

※ 剪走某資料夾中所有的檔案到特定資料夾

find . -type f -mtime +0 -exec mv {} /home/michael/AServer/LogFiles \;

※ 刪除某資料夾中所有檔案到特定資料夾

find /home/michael/AServer/LogFiles/* -mtime +0 -exec rm -rf {} \;

※ 利用 scp 將某資料夾中所有檔案 搬移到 B 伺服器的底下

scp /home/michael/AServer/LogFiles/* root@10.10.60.32:/home/micael/game4-4/

※ 解壓縮 .tar.gz 的類型檔案到特定資料夾

tar -zxvf TWSERVER_100901_BUILD.tar.gz -C /home/michael/AServer/new/

※ 查詢 OS Version

lsb_release -a

※ 壓縮 /home/Michael/GMServer <= 此路徑底下的所有檔案，其檔名為 gmserver.tar

tar -cvf gmserver.tar /home/Michael/GMServer

Windows Server 啟動 MySQL 出現 1067 Error

此錯誤應該是您安裝時選擇的不是預設路徑

修正 my.ini 該檔應該就可以

該檔路徑：C:\WINDOWS\my.ini

[WinMySQLAdmin]

Server=D:/mysql/bin/mysqld-nt.exe

初次開啟此檔的時候會像如上文字敘述

加入以下訊息即可，我是裝在 D:\

[mysqld]

#setting basedir to mysql install side

basedir=D:\mysql

datadir=D:\mysql\data

修正好後存檔，在去服務中執行 MySQL。

應該就可以處理您的問題

about Windows SAM File

所有 Windows 帳號的密碼都存在所謂的 SAM File

而如果忘記密碼該如何處理？

方法一 (沒意義)
光碟啟動，安裝第二套系統（NT/2000/XP/03均可），進入第二套系統，刪除SAM文件
方法二 (很建議)
取下硬碟，掛到另一台電腦上，刪除SAM文件
方法三 (沒A磁碟機就搞笑，所以有USB軟碟機是很正常的事情，只是快買不到了)
利用NTgFSDOS（在網上可搜尋到這類軟體）軟碟啟動電腦，刪除SAM文件
方法四 (現在應該找不到98嚕)
如果你的WINNT夾在FAT分區上，也可以直接利用98啟動碟/98光碟啟動電腦，刪除SAM文件。

以上是網路上找到的段落，但是通常都不用那麻煩

請使用 Active Password Changer 目前好像是出到 3.8

燒在光碟中 開機執行就好嚕！英文操作介面 不難啦

於批次檔暫停n秒的小技巧

資料來源：http://blog.miniasp.com/post/2009/06/Sleep-command-in-Batch.aspx

1. 利用 PING 指令幫忙停 5 秒

每壹台電腦都有 PING 執行檔，這個最好用啦!

@ping 127.0.0.1 -n 5 -w 1000 > nul

2. 利用 CHOICE 指令

CHOICE 命令在 Windows XP 中找不到，但在 Windows Server 2003 或 Vista 都有內建。

@CHOICE /C YN /N /T 5 /D y > nul

3. 安裝 Windows Server 2003 Resource Kit Tools 即可獲得 sleep.exe 工具

預設安裝路徑在 C:\Program Files\Windows Resource Kits\Tools 目錄下會有個 sleep.exe 執行檔

sleep 5

Windows Login Type

Windows Logon Type

工作需要 總是會要記一堆東西...

Logon type 2 Interactive 本地登錄。最常見的登錄方式。
Logon type 3 Network 網絡登錄- 最常見的是訪問網絡共享文件夾或打印機。 IIS的認證也是Type 3
Logon type 4 Batch 計劃任務
Logon Type 5 Service 服務
某些服務是用一個域帳號來運行的，出現Failure常見的情況是管理員更改了域帳號密碼，但是忘記重設Service中的帳號密碼。
Logon Type 7 Unlock 解除屏幕鎖定
很多公司都有這樣的安全設置：當用戶離開屏幕一段時間後，屏保程序會鎖定計算機屏幕。解開屏幕鎖定需要鍵入用戶名和密碼。此時產生的日誌類型就是Type 7
Logon Type 8 NetworkCleartext 網絡明文登錄-- 通常發生在IIS 的ASP登錄。不推薦
Logon Type 9 NewCredentials 新身份登錄-- 通常發生在RunAS方式運行某程序時的登錄驗證。
Logon Type 10 RemoteInteractive 遠程登錄-- 比如Terminal service或者RDP方式。但是Windows 2000是沒有Type10的，用Type 2。 WindowsXP/2003起有Type 10
Logon Type 11 CachedInteractive 緩存登錄
為方便筆記本電腦用戶，Windows會緩存前10次成功登錄的登錄。

以上是簡字翻成繁體我快昏倒..還好有找到以下原文

附原文：
The logon/logoff category of the Windows security log gives you the ability to monitor all attempts to access the local computer. In this article I’ll examine each logon type in greater detail and show you how some other fields in Logon/Logoff events can be helpful for understanding the nature of a given logon attempt.

Event IDs 528 and 540 signify a successful logon, event ID 538 a logoff and all the other events in this category identify different reasons for a logon failure. However, just knowing about a successful or failed logon attempt doesn’t fill in the whole picture. Because of all the services Windows offers, there are many different ways you can logon to a computer such as interactively at the computer’s local keyboard and screen, over the network through a drive mapping or through terminal services (aka remote desktop) or through IIS. Thankfully, logon/logoff events specify the Logon Type code which reveals the type of logon that prompted the event.

Logon Type 2 – Interactive
This is what occurs to you first when you think of logons, that is, a logon at the console of a computer. You’ll see type 2 logons when a user attempts to log on at the local keyboard and screen whether with a domain account or a local account from the computer’s local SAM. To tell the difference between an attempt to logon with a local or domain account look for the domain or computer name preceding the user name in the event’s description. Don’t forget that logon’s through an KVM over IP component or a server’s proprietary “lights-out” remote KVM feature are still interactive logons from the standpoint of Windows and will be logged as such.

Logon Type 3 – Network
Windows logs logon type 3 in most cases when you access a computer from elsewhere on the network. One of the most common sources of logon events with logon type 3 is connections to shared folders or printers. But other over-the-network logons are classed as logon type 3 as well such as most logons to IIS. (The exception is basic authentication which is explained in Logon Type 8 below.)

Logon Type 4 – Batch
When Windows executes a scheduled task, the Scheduled Task service first creates a new logon session for the task so that it can run under the authority of the user account specified when the task was created. When this logon attempt occurs, Windows logs it as logon type 4. Other job scheduling systems, depending on their design, may also generate logon events with logon type 4 when starting jobs. Logon type 4 events are usually just innocent scheduled tasks startups but a malicious user could try to subvert security by trying to guess the password of an account through scheduled tasks. Such attempts would generate a logon failure event where logon type is 4. But logon failures associated with scheduled tasks can also result from an administrator entering the wrong password for the account at the time of task creation or from the password of an account being changed without modifying the scheduled task to use the new password.

Logon Type 5 – Service
Similar to Scheduled Tasks, each service is configured to run as a specified user account. When a service starts, Windows first creates a logon session for the specified user account which results in a Logon/Logoff event with logon type 5. Failed logon events with logon type 5 usually indicate the password of an account has been changed without updating the service but there’s always the possibility of malicious users at work too. However this is less likely because creating a new service or editing an existing service by default requires membership in Administrators or Server Operators and such a user, if malicious, will likely already have enough authority to perpetrate his desired goal.

Logon Type 7 – Unlock
Hopefully the workstations on your network automatically start a password protected screen saver when a user leaves their computer so that unattended workstations are protected from malicious use. When a user returns to their workstation and unlocks the console, Windows treats this as a logon and logs the appropriate Logon/Logoff event but in this case the logon type will be 7 – identifying the event as a workstation unlock attempt. Failed logons with logon type 7 indicate either a user entering the wrong password or a malicious user trying to unlock the computer by guessing the password.

Logon Type 8 – NetworkCleartext
This logon type indicates a network logon like logon type 3 but where the password was sent over the network in the clear text. Windows server doesn’t allow connection to shared file or printers with clear text authentication. The only situation I’m aware of are logons from within an ASP script using the ADVAPI or when a user logs on to IIS using IIS’s basic authentication mode. In both cases the logon process in the event’s description will list advapi. Basic authentication is only dangerous if it isn’t wrapped inside an SSL session (i.e. https). As far as logons generated by an ASP, script remember that embedding passwords in source code is a bad practice for maintenance purposes as well as the risk that someone malicious will view the source code and thereby gain the password.

Logon Type 9 – NewCredentials
If you use the RunAs command to start a program under a different user account and specify the /netonly switch, Windows records a logon/logoff event with logon type 9. When you start a program with RunAs using /netonly, the program executes on your local computer as the user you are currently logged on as but for any connections to other computers on the network, Windows connects you to those computers using the account specified on the RunAs command. Without /netonly Windows runs the program on the local computer and on the network as the specified user and records the logon event with logon type 2.

Logon Type 10 – RemoteInteractive
When you access a computer through Terminal Services, Remote Desktop or Remote Assistance windows logs the logon attempt with logon type 10 which makes it easy to distinguish true console logons from a remote desktop session. Note however that prior to XP, Windows 2000 doesn’t use logon type 10 and terminal services logons are reported as logon type 2.

Logon Type 11 – CachedInteractive
Windows supports a feature called Cached Logons which facilitate mobile users. When you are not connected to the your organization’s network and attempt to logon to your laptop with a domain account there’s no domain controller available to the laptop with which to verify your identity. To solve this problem, Windows caches a hash of the credentials of the last 10 interactive domain logons. Later when no domain controller is available, Windows uses these hashes to verify your identity when you attempt to logon with a domain account.

Conclusion
I hope this discussion of logon types and their meanings helps you as you keep watch on your Windows network and try to piece together the different ways users are accessing your computers. Paying attention to logon type is important because different logon types can affect how you interpret logon events from a security perspective. For instance a failed network logon on a server might now be surprising since users must access servers over the network all the time. But a failed network logon attempt in a workstation security log is different. Why is anyone trying to access someone else’s workstation from over the network? As you can see, it pays to understand the security log.

Windows Live Messenger 2009 Service Port

http://support.microsoft.com/kb/960820

Teamviewer Block

今日閒來無事，抓了一下 Teamviewer 此套好用工具的網路封包。

因此將此軟體服務 Port 加入防火牆鎖定。

其 Port 為 TCP 5938 將此加入防火牆阻擋清單即可。

一開始原本是想要利用 IP 的方式阻擋，但是檔了一個軟體換跳一個。一整個被耍...

我們公司又沒有採買 App Filter 所以由軟體執行 Port 去做阻擋就對啦。

小弟公司最近有小朋友要遠端回來的需求，因此利用此鎖定加上防火牆 Schedule 即可。

會用這套是因為...傻瓜也會用 就不用再次做教育訓練的必要 XD

Windows Logon Event ID Log Type

有助於伺服器維護時的系統查核保養

資料來源：http://technet.microsoft.com/en-us/library/cc787053(WS.10).aspx

Event ID	Description
528	Successful logon.
529	Logon failure. Unknown user name or bad password.
530	Logon failure. Account logon time restriction violation.
531	Logon failure. The account is currently disabled.
532	Logon failure. The specified user account has expired.
533	Logon failure. The user is not allowed to log on at this computer.
534	Logon failure. The user has not been granted the requested logon type at this computer.
535	Logon failure. The specified account’s password has expired.
536	Logon failure. The NetLogon component is not active.
537	Logon failure. An unexpected error occurred during logon.
538	User logoff. This event is generated when the logoff process is complete. A logoff is considered complete when the associated logon session object is deleted, which occurs after all tokens associated with the logon session are closed. This can take an arbitrarily long time; this event should not be used to calculate the total logon duration. Instead, use event 551.
539	Logon failure. Account locked out.
540	Successful network logon.
541	IPSec security association established.
542	IPSec security association ended. Mode: Data Protection (Quick mode).
543	IPSec security association ended. Mode: Key Exchange (Main mode).
544	IPSec security association establishment failed because peer could not authenticate. The certificate trust could not be established.
545	IPSec peer authentication failed.
546	IPSec security association establishment failed because peer sent invalid proposal.
547	IPSec security association negotiation failed.
548	Logon failure. Domain security identifier (SID) is inconsistent. This event is generated when a user account from a trusted domain attempts to authenticate, but the domain SID does not match the SID stored in the Trusted Domain Object (TDO).
549	Logon failure. All SIDs were filtered out. During authentication across forests, SIDs corresponding to untrusted namespaces are filtered out. This event is generated when all SIDs are filtered. This event is generated on the Kerberos Key Distribution Center (KDC). This event is not generated on Windows Server 2003.
550	Notification message that can indicate a possible denial-of-service attack.
551	User-initiated logoff. This event is generated when the user initiates the logoff process. When the logoff process is complete, event 538 is logged.
552	Successful logon. This event is generated when a user logs on with explicit credentials while already logged on as another user. This event is logged when using the RunAs tool.
553	Logon failure. This event is generated when an authentication package detects a replay attack.