public void TestCsvReadWrite() {
string fileName = Path.GetTempFileName();
CsvFile csvDoc = new CsvFile();
csvDoc.Headers.AddRange(new string[] {"item1", "item2"});
csvDoc.Records.Add(new string[] {"a", "1"});
csvDoc.Records.Add(new string[] {"b,c", "2"});
csvDoc.Records.Add(new string[] {"\"d\", \"e\"", "3"});
Assert.DoesNotThrow(
delegate() {
csvDoc.Write(fileName);
},
"CsvFile.Write() should not throw an exception.");
// Downloaded report should be a valid csv.
csvDoc = new CsvFile();
Assert.DoesNotThrow(
delegate() {
csvDoc.Read(fileName, true);
},
"CsvFile should not throw an exception.");
Assert.AreEqual(csvDoc.Headers.Count, 2);
Assert.AreEqual(csvDoc.Headers[0], "item1");
Assert.AreEqual(csvDoc.Headers[1], "item2");
Assert.AreEqual(csvDoc.Records.Count, 3);
Assert.AreEqual(csvDoc.Records[0][0], "a");
Assert.AreEqual(csvDoc.Records[0][1], "1");
Assert.AreEqual(csvDoc.Records[1][0], "b,c");
Assert.AreEqual(csvDoc.Records[1][1], "2");
Assert.AreEqual(csvDoc.Records[2][0], "\"d\", \"e\"");
Assert.AreEqual(csvDoc.Records[2][1], "3");
}
/// <summary>
/// Run the code example.
/// </summary>
/// <param name="user">The DFP user object running the code example.</param>
public override void Run(DfpUser user) {
// Get the InventoryService.
PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService) user.GetService(
DfpService.v201306.PublisherQueryLanguageService);
int pageSize = 500;
// Create statement to select all targetable cities.
String selectStatement = "SELECT * FROM City WHERE targetable = true LIMIT " + pageSize;
int offset = 0;
int resultSetSize = 0;
List<Row> allRows = new List<Row>();
ResultSet resultSet;
try {
do {
// For criteria that do not have a "targetable" property, that predicate
// may be left off, i.e. just "SELECT * FROM Browser_Groups LIMIT 500"
StatementBuilder statementBuilder =
new StatementBuilder(selectStatement + " OFFSET " + offset);
// Get all cities.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all cities from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
offset += pageSize;
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == pageSize);
Console.WriteLine("Number of results found: " + allRows.Count);
// Optionally, save all rows to a CSV.
// Get a string array representation of the data rows.
resultSet.rows = allRows.ToArray();
List<String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write("cities.csv");
} catch (Exception ex) {
Console.WriteLine("Failed to get all cities. Exception says \"{0}\"", ex.Message);
}
}
/// <summary>
/// Run the code example.
/// </summary>
/// <param name="user">The DFP user object running the code example.</param>
public override void Run(DfpUser user)
{
// Get the PublisherQueryLanguageService.
PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService) user.GetService(
DfpService.v201308.PublisherQueryLanguageService);
int pageSize = 500;
// Create statement to select all line items.
String selectStatement = "SELECT Id, Name, Status FROM Line_Item where Name LIKE " +
"'line item%' order by Id ASC LIMIT " + pageSize;
int offset = 0;
int resultSetSize = 0;
List<Row> allRows = new List<Row>();
ResultSet resultSet;
try {
do {
StatementBuilder statementBuilder =
new StatementBuilder(selectStatement + " OFFSET " + offset);
// Get line items like 'line item%'.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all line items from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
offset += pageSize;
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == pageSize);
Console.WriteLine("Number of results found: " + allRows.Count);
// Optionally, save all rows to a CSV.
// Get a string array representation of the data rows.
resultSet.rows = allRows.ToArray();
List<String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write("line_items_named_like_" + GetTimeStamp() + ".csv");
} catch (Exception ex) {
Console.WriteLine("Failed to get line items. Exception says \"{0}\"", ex.Message);
}
}
/// <summary>
/// Run the code example.
/// </summary>
/// <param name="user">The DFP user object running the code example.</param>
public override void Run(DfpUser user) {
// Get the PublisherQueryLanguageService.
PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService) user.GetService(
DfpService.v201411.PublisherQueryLanguageService);
// Create statement to select all line items.
StatementBuilder statementBuilder = new StatementBuilder()
.Select("Id, Name, Status")
.From("Line_Item")
.OrderBy ("id ASC")
.Limit(StatementBuilder.SUGGESTED_PAGE_LIMIT);
int resultSetSize = 0;
List<Row> allRows = new List<Row>();
ResultSet resultSet;
try {
do {
// Get all line items.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all line items from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
statementBuilder.IncreaseOffsetBy(StatementBuilder.SUGGESTED_PAGE_LIMIT);
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == StatementBuilder.SUGGESTED_PAGE_LIMIT);
Console.WriteLine("Number of results found: " + allRows.Count);
// Optionally, save all rows to a CSV.
// Get a string array representation of the data rows.
resultSet.rows = allRows.ToArray();
List<String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write("line_items_" + this.GetTimeStamp() + ".csv");
} catch (Exception ex) {
Console.WriteLine("Failed to get all line items. Exception says \"{0}\"", ex.Message);
}
}
/// <summary>
/// Fetches a match table from a PQL statement and writes it to a file.
/// </summary>
/// <param name="pqlService">The PQL service.</param>
/// <param name="selectStatement">The select statement.</param>
/// <param name="fileName">Name of the file.</param>
private static void fetchMatchTables(PublisherQueryLanguageService pqlService,
StatementBuilder statementBuilder, string fileName) {
int resultSetSize = 0;
List<Row> allRows = new List<Row>();
ResultSet resultSet;
do {
resultSet = pqlService.select(statementBuilder.ToStatement());
allRows.AddRange(resultSet.rows);
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
statementBuilder.IncreaseOffsetBy(StatementBuilder.SUGGESTED_PAGE_LIMIT);
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == StatementBuilder.SUGGESTED_PAGE_LIMIT);
resultSet.rows = allRows.ToArray();
List<String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write(fileName);
}
/// <summary>
/// Runs the code example.
/// </summary>
/// <param name="user">The AdWords user.</param>
/// <param name="startDate">The start date for tracking performance.</param>
/// <param name="endDate">The end date for tracking performance.</param>
/// <param name="fileName">The file to which the budget utilization report is saved.</param>
public void Run(AdWordsUser user, string startDate, string endDate, string fileName)
{
string outputPath = ExampleUtilities.GetHomeDir() + Path.DirectorySeparatorChar + fileName;
Dictionary<long, LocalCampaign> campaigns = FetchCampaignStats(user, startDate, endDate);
int numDays = GetDaysBetween(startDate, endDate);
CsvFile csvFile = new CsvFile();
csvFile.Headers.AddRange(new string[] {
"CampaignId", "CampaignName", "DailyBudget", "Clicks", "Impressions", "Cost",
"Lost Clicks", "Lost Impressions", "Lost Cost", "Difference in Amount"
});
foreach (long campaignId in campaigns.Keys) {
LocalCampaign campaign = campaigns[campaignId];
List<string> row = new List<string>(new string[] {
campaign.CampaignId.ToString(),
campaign.CampaignName,
campaign.BudgetAmount.ToString(),
campaign.Stats.Clicks.ToString(),
campaign.Stats.Impressions.ToString(),
campaign.Stats.Cost.ToString(),
campaign.Stats.BudgetLostClicks.ToString(),
campaign.Stats.BudgetLostImpressions.ToString(),
campaign.Stats.BudgetLostCost.ToString()
});
if (campaign.Stats.BudgetLostCost > 0) {
// Extra budget needed is the same as BudgetLostCost.
row.Add(campaign.Stats.BudgetLostCost.ToString());
} else {
// Surplus budget is the same as cost - budget.
row.Add((campaign.Stats.Cost - campaign.BudgetAmount * numDays).ToString());
}
csvFile.Records.Add(row.ToArray());
}
csvFile.Write(outputPath);
}
/// <summary>
/// Run the code example.
/// </summary>
/// <param name="user">The DFP user object running the code example.</param>
public override void Run(DfpUser user) {
// Get the PublisherQueryLanguageService.
PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService) user.GetService(
DfpService.v201511.PublisherQueryLanguageService);
string geoType = "City";
// Create statement to select all targetable cities.
StatementBuilder statementBuilder = new StatementBuilder()
.Select("Id, Name, CanonicalParentId, ParentIds, CountryCode")
.From("Geo_Target")
.Where("Type = :type and Targetable = true")
.OrderBy("id ASC")
.Limit(StatementBuilder.SUGGESTED_PAGE_LIMIT)
.AddValue("type", geoType);
int resultSetSize = 0;
List<Row> allRows = new List<Row>();
ResultSet resultSet;
try {
do {
// Get all cities.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all cities from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
statementBuilder.IncreaseOffsetBy(StatementBuilder.SUGGESTED_PAGE_LIMIT);
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == StatementBuilder.SUGGESTED_PAGE_LIMIT);
Console.WriteLine("Number of results found: " + allRows.Count);
// Optionally, save all rows to a CSV.
// Get a string array representation of the data rows.
resultSet.rows = allRows.ToArray();
List<String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write(geoType + "_" + this.GetTimeStamp() + ".csv");
} catch (Exception e) {
Console.WriteLine("Failed to get Geo type = '{0}'. Exception says \"{1}\"",
geoType, e.Message);
}
}
/// <summary>
/// Fetches a match table from a PQL statement and writes it to a file.
/// </summary>
/// <param name="pqlService">The PQL service.</param>
/// <param name="selectStatement">The select statement.</param>
/// <param name="fileName">Name of the file.</param>
private static void fetchMatchTables(PublisherQueryLanguageService pqlService,
string selectStatement, string fileName) {
int pageSize = 500;
Statement statement = new StatementBuilder(selectStatement).ToStatement();
int offset = 0;
int resultSetSize = 0;
List<Row> allRows = new List<Row>();
ResultSet resultSet;
do {
statement.query = selectStatement + " limit " + pageSize + " OFFSET " + offset;
resultSet = pqlService.select(statement);
allRows.AddRange(resultSet.rows);
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
offset += pageSize;
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == pageSize);
resultSet.rows = allRows.ToArray();
List<String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write(fileName);
}
/// <summary>
/// Run the code example.
/// </summary>
/// <param name="user">The DFP user object running the code example.</param>
public override void Run(DfpUser user) {
// Get the PublisherQueryLanguageService.
PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService) user.GetService(
DfpService.v201311.PublisherQueryLanguageService);
string geoType = "City";
int pageSize = 500;
// Create statement to select all targetable cities.
String selectStatement = "SELECT Id, Name, CanonicalParentId, ParentIds, CountryCode from " +
"Geo_Target where Type = :type and Targetable = true order by CountryCode ASC, " +
"Name ASC limit " + pageSize;
Statement statement = new StatementBuilder(selectStatement).AddValue("type", geoType)
.ToStatement();
int offset = 0;
int resultSetSize = 0;
List<Row> allRows = new List<Row>();
ResultSet resultSet;
try {
do {
statement.query = selectStatement + " OFFSET " + offset;
// Get all cities.
resultSet = pqlService.select(statement);
// Collect all cities from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
offset += pageSize;
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == pageSize);
Console.WriteLine("Number of results found: " + allRows.Count);
// Optionally, save all rows to a CSV.
// Get a string array representation of the data rows.
resultSet.rows = allRows.ToArray();
List<String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write(geoType + "_" + this.GetTimeStamp() + ".csv");
} catch (Exception ex) {
Console.WriteLine("Failed to get Geo type = '{0}'. Exception says \"{1}\"",
geoType, ex.Message);
}
}